Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
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
  • No labels