如何筛选筛选范围以查看是否有任何单元格包含某些文本

Question

到目前为止，我的代码的每个部分都可以工作，我只想编辑一部分。我让我的宏搜索过滤范围，看看它是否包含"已签入"和"已签出"。

但是，我想添加更多单词来检查。有什么方法可以更改此代码以制作搜索每个单元格的字符串数组？

我想我可以添加很多"if or"，但这并不好玩。

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

Answer 1

是的，当然你可以创建数组并循环遍历数组

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