CODEWITHSUNDEEP
vbastringcompare
kelsey
kelsey

Reputation: 13

VBA detect and compare string

I managed to get the answer after I run this code. But I still received Runtime error 13 message. Can u help me to resolve it? @PEH

 Dim ws As Worksheet
 Dim str As String

 str = "Attention for shipment on track"

 Set ws = Worksheets("Report")

 Dim lastrows As Long
 lastrows = ws.cells(Rows.Count, 1).End(xlUp).Row

 Dim i As Long
 For i = 2 To lastrows
    If InStr(1, ws.cells(i, 43).Value, str) > 0 Then
        ws.cells(i, 63).Value = "OK"
    End If
  Next i

Upvotes: 1

Views: 109

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Note that InStr returns a position and not True or False what is needed for the If statement. Also "str" is litterally looking for these 3 characters "str" if you mean the variable str you need to remove the quotes here.

Finally the first parameter of the InStr function is the start position.
As @FunThomas pointed out in the comments the Start parameter in the beginning of the InStr function is optional and indeed can be omited.

Option Explicit

Sub track()        
    Dim str As String
    str = "Attention for shipment on track"

    Dim ws As Worksheet
    Set ws = Worksheets("Report")

    Dim lastrows As Long
    lastrows = ws.cells(Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastrows         
        If InStr(1, ws.cells(i, 43).Value, str) > 0 Then
            ws.cells(i, 63).Value = "OK"  
        End If
    Next i
End Sub

Make sure always to use Option Explicit and declare all variables properly.

Upvotes: 2

Related Questions