Sub SearchStrings() Dim r As Range For Each r In ActiveSheet.UsedRange v = r.Value If InStr(v, "TEST") > 0 Or InStr(v, "StrInG") > 0 Then 'Note is CASE Sensitive, but will return true for cell value of "TEST-foobar" MsgBox "StrFound" End If Next r End Sub
Public Sub InString() Dim rcell As Range, rng As Range Set rng = Application.ActiveSheet.Range("B2:B" & Application.ActiveSheet.UsedRange.Rows.Count) For Each rcell In rng.Cells If InStr(1, Range(rcell.Address), "Test", vbTextCompare) <> 0 Then MsgBox "Match In " & rcell.Address(False, False) End If Next rcell End Sub
https://stackoverflow.com/questions/24972335/multiple-string-search-with-instr-function-in-vba
Public Sub InString_Test() Dim WS As Worksheet Set WS = ThisWorkbook.Sheets("Sheet1") Dim rcell As Range, rng As Range Set rng = WS.Range("A1:A" & WS.UsedRange.Rows.Count) For Each rcell In rng.Cells If InStrFunc(Range(rcell.Address), "TEST", "CAT") Then MsgBox "String Found in " & rcell.Address End If Next rcell End Sub Function InStrFunc(strCheck As String, ParamArray anyOf()) As Boolean Dim item As Long For item = 0 To UBound(anyOf) If InStr(1, strCheck, anyOf(item), vbTextCompare) <> 0 Then InStrFunc = True Exit Function End If Next End Function