VBA detect and compare string

Question

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

I don't know if that will solve your problem but i'd change If InStr(ws.cells(i, 43).Value, "str") Then for If ws.Cells(i, 43) Like "*" & str & "*" Then — Damian
– Damian, Commented Feb 16, 2021 at 9:23
That must be, but using the Like operator in this context would be enough for her goal, right? — Damian
– Damian, Commented Feb 16, 2021 at 9:25
Which line shows this error message? If you cannot tell the line of code that produces that error we cannot help you. — Pᴇʜ
– Pᴇʜ, Commented Mar 1, 2021 at 13:57
This line " If InStr(1, ws.cells(i, 43).Value, str) > 0 Then ". I still got result but when I tried to debug, the error message Run-tim13 (Type mismatch) pops out. Is there anything I missed out? — kelsey
– kelsey, Commented Mar 4, 2021 at 8:16
@kelsey what is the value of ws.cells(i, 43).Value in case of the error? — Pᴇʜ
– Pᴇʜ, Commented Mar 4, 2021 at 8:18

Pᴇʜ · Accepted Answer · 2021-02-16 10:32:50Z

2

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.

edited Feb 16, 2021 at 10:32

answered Feb 16, 2021 at 9:22

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

7 Comments

FunThomas Over a year ago

You can omit the starting position for InStr in VBA (even if it is the first parameter).

Pᴇʜ Over a year ago

@FunThomas huh, indeed, you are right. I was 100 % sure if you omit a paramater in the beginning you have to use named parameters instead to make it work. Another example in my list of how strange VBA sometimes is. Fixed it!

Dy.Lee Over a year ago

Any number other than 0 in vba returns true. Therefore, it is not necessary to distinguish between true or false by comparing with >0. In the vlookup function, 0,1 is used instead of FALSE and TRUE.

Dy.Lee Over a year ago

Boolean Data Type (Visual Basic) refer This

Pᴇʜ Over a year ago

@Dy.Lee You are right, that's not an issue, as 0 is defined to be False and True is defined not to be False. I got confused because in VBA True casts to -1 and in formulas True casts to 1. But yes, technically that's not an issue. Still I would prefer to be clear here not to lead to misunderstandings.

|

Collectives™ on Stack Overflow

VBA detect and compare string

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related