How to sift through a filtered range to see if any cells contain certain text

Question

Every part of my code works thus far, I just want to edit one part. I have my macro searching through a filtered range to see if it contains "CHECKED IN" and "CHECKED OUT".

However I want to add many more words to check for. Is there any way I can change this code to perhaps make an array of strings that each cell is searched for?

I suppose I can add a lot of "if or"s, but that isn't fun.

Sub checkk()
Dim cl As Range, rng As Range
Dim LastRow As Long
Dim celltxt As String
Dim i As Integer

i = 1

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set rng = Range("A1:A" & LastRow)

For Each cl In rng.SpecialCells(xlCellTypeVisible)
cl.Select
celltxt = ActiveCell.Text
If InStr(1, celltxt, "CHECKED OUT") Or InStr(1, celltxt, "CHECKED IN") Then
MsgBox ("found it")
else
MsgBox ("no")
End If



Next cl

If i > 1 Then
MsgBox ("Looks like you have to do some more filtering, sort column A by 
color to see what was tagged")

End If


End Sub

Possible duplicate of [Count and Highlight keywords within phrases](https://stackoverflow.com/questions/32860792/count-and-highlight-keywords-within-phrases) and/or [Pattern matching in excel](https://stackoverflow.com/questions/32946884/pattern-matching-in-excel). — , Nov 03 '17 at 23:05

score 1 · Accepted Answer · answered Nov 03 '17 at 22:52

Yes of course you can create array and loop through array

Sub checkk()
    Dim cl As Range, rng As Range
    Dim LastRow As Long
    Dim celltxt As String
    Dim arrVarToCheck(2) As Variant

    arrVarToCheck(0) = "CHECKED OUT"
    arrVarToCheck(1) = "CHECKED IN"
    arrVarToCheck(2) = "Foo"

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Set rng = Range("A1:A" & LastRow)

    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        celltxt = cl.Text
        For i = 0 To UBound(arrVarToCheck)
            If InStr(1, celltxt, arrVarToCheck(i)) Then
                MsgBox ("found it")
            Else
                MsgBox ("no")
            End If
        Next i
    Next cl
End Sub

How to sift through a filtered range to see if any cells contain certain text

1 Answers1