excelvbainstr

Can InStr be used to find multiple String1 values?


I'm manipulating some data, by searching through a list and and printing out the row numbers of the cells that match the criteria I'm looking for. I'm using InStr to do this - can this be used to search for multiple items?

I've tried "Or", "And" etc but they don't seem to work, and there are a lot of criteria I'm looking for so would prefer not to have lots of If statements.

For Each cell In SrchRng
    If InStr(1, cell.Value, "A" or "B") > 0 Then


   'Print row number...

     End If
Next cell

End Sub

Run-time error "13" Type mismatch

occurs with the above. The actual output would be the row number that "A" or "B" occurs in.


Solution

  • You can try addition to shorten your code a bit and also perhaps make it more readable:

    If InStr(1, cell.Value, "A") + _
       InStr(1, cell.Value, "B") > 0 Then
         'do stuff
    End If