VBA: statement in if Then loop fails

Question

I have a sheet with Columns A to P. In columns B i have customer names. Want to find rows with substring “ABC -“ and copy the content of the cell in column B to Column G on the same row.

My code fails on this:

For I= 1 to finalrow
    If Left(Cells(I,2).Value,5) = “ABC -“ Then
Rownumber= ActiveCell.Row
Range("B" & Rownumber).Select
Range("B" & Rownumber).Copy
        Range("G" & rownumber).Select
        ActiveSheet.Paste
        Range("G" & rownumber).Select
End if

Next I

"My code fails on this" -- what do you mean exactly? What error do you get? — C. Peck, May 11 '21 at 07:38

score 3 · Answer 1 · answered May 10 '21 at 12:29

3

This one works as expected, writing the values from column "B" to column "G":

Sub TestMe()
    
    Dim i As Long
    For i = 1 To 10
        With ThisWorkbook.Worksheets("Sheet1")
            Dim myCell As Range
            Set myCell = .Cells(i, "B")
            If Trim(Left(myCell.Value, 5)) = "ABC -" Then
                .Cells(i, "G").Value = myCell.Value
            End If
        End With
    Next i

End Sub

Try to avoid .Select and .Activate - https://stackoverflow.com/a/35864330/5448626
Use Trim()
Using . and referring the worksheet is always a good practice
.Cells(i, "B") improves readability
“ probably should be "

answered May 10 '21 at 12:29

Vityata

42,633
8
55
100

Thank you, it works fine. Using the . does it mean, that I refer to the reference after the With statement – sober May 19 '21 at 21:01
@sober - you are welcome. The dot means that the line refers to the With, yes. E.g. `.Cells(i, "G").Value` is absolutely equal to `ThisWorkbook.Worksheets("Sheet1").Cells(i, "G").Value`. – Vityata May 20 '21 at 06:28

score 1 · Answer 2 · answered May 10 '21 at 12:33

1

For I = 1 To finalrow
    With Cells(I, 2)
        If .Text Like "ABC -*" Then .Offset(0, 5) = .Value
    End With
Next I

answered May 10 '21 at 12:33

Алексей Р

7,507
2
7
18

score 0 · Answer 3 · answered May 10 '21 at 12:21

0

For I = 1 to finalrow
    If Left(Cells(I,2).Value,5) = "ABC -" Then
       Cells(I,7).Value = Cells(I,2).Value
    End if
Next I

answered May 10 '21 at 12:21

Nicholas Hunter

1,791
1
11
14

VBA: statement in if Then loop fails

3 Answers3