CODEWITHSUNDEEP
excelvbaif-statement
sober
sober

Reputation: 1

VBA: statement in if Then loop fails

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

Upvotes: 0

Views: 52

Answers (3)

Алексей Р
Алексей Р

Reputation: 7627

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

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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

Upvotes: 3

Nicholas Hunter
Nicholas Hunter

Reputation: 1845

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

Upvotes: 0

Related Questions