Search code examples
excelvba

Copy and paste if the cell value starts with certain letter


In Column A, there is data which contain question, choices and other data. The choices starts with A B C D in each row. I just need to copy the CHOICES and QUESTION and paste into next Column B. Sample data as shown below.

I tried the below code, but it copies only the choices no the question whihc is above choice A. I need help to fix the code. Also this code runs slowly.

ColA ColB
Program Math
Exercise 3-24
This is a sample test
Select the correct answer
1 Question 1 Question
A choice-1 A choice-1
B choice-2 B choice-2
C choice-3 C choice-3
D choice-4 D choice-4
Program Math
Exercise 5-12
This is a sample test
Select the correct answer
2 Question 2 Question
A choice-1 A choice-1
B choice-2 B choice-2
C choice-3 C choice-3
D choice-4 D choice-4
Program Math
Exercise 2-14
This is a sample test
Select the correct answer
1 Question
A choice-1
B choice-2
C choice-3
D choice-4
Sub CopyPasteChoices()

Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range

Sheet2.Activate

For Each a In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If a.Value Like "A *" Then
        a.Copy Destination:=a.Offset(0, 2)
    End If
Next a

For Each b In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If b.Value Like "B *" Then
        b.Copy Destination:=b.Offset(0, 2)
    End If
Next b

For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If c.Value Like "C *" Then
        c.Copy Destination:=c.Offset(0, 2)
    End If
Next c

For Each d In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If d.Value Like "D *" Then
        d.Copy Destination:=d.Offset(0, 2)
    End If
Next d

End Sub

Solution

  • You can combine the criterias into one loop with including the check for the Question. This will also speed up the execution.

    Sub CopyPasteChoices()
    
    Dim a As Range
    
    Sheet2.Activate
    
    For Each a In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If a.Value Like "A *" Then a.Offset(-1).Copy Destination:=a.Offset(-1, 2)
        If a.Value Like "A *" Or a.Value Like "B *" Or a.Value Like "C *" Or a.Value Like "D *" Then
            a.Copy Destination:=a.Offset(0, 2)
        End If
    Next a
    
    
    End Sub