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
– C. Peck, Commented May 11, 2021 at 7:38

Vityata · Accepted Answer · 2021-05-10 12:29:37Z

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, 2021 at 12:29

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sober Over a year ago

Thank you, it works fine. Using the . does it mean, that I refer to the reference after the With statement

Vityata Over a year ago

@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.

Алексей Р · Accepted Answer · 2021-05-10 12:33:25Z

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, 2021 at 12:33

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Comments

Nicholas Hunter · Accepted Answer · 2021-05-10 12:21:25Z

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, 2021 at 12:21

Nicholas Hunter

1,8561 gold badge12 silver badges15 bronze badges

Collectives™ on Stack Overflow

VBA: statement in if Then loop fails

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related