vba - Excel VBA 在整列中查找文本

Question

我有以下代码可以在“I”列中找到短语“请查看”，如果没有找到显示消息，如果找到它必须运行我的其余代码，但它不喜欢我的 IF 代码:

Sub OUTPUT()

    Sheets("OUTPUT").Select
    If Range("a2").Value < 1 Then
    Else
    Range("A2:I" & Range("A1").End(xlDown).Row + 1).ClearContents
    End If
    Sheets("SF Data").Select
    If Range("I2:I192754").Value <> "Please Review" Then
    MsgBox "Nudda"
    Else
    Columns("A:I").Select
    Selection.AutoFilter
    Range("I2").Select
    ActiveSheet.Range("A1:I" & Range("A1").End(xlDown).Row + 1).AutoFilter Field:=9, Criteria1:= _
        "Please Review"
    Range("A2:I" & Range("A1").End(xlDown).Row + 1).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("OUTPUT").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("SF Data").Select
    ActiveSheet.Range("$A$1:$I$192754").AutoFilter Field:=9
    Sheets("OUTPUT").Select
End If
MsgBox "Sanity Check performed. " & Format(Now, "mmmm d, yyyy hh:mm AM/PM")
End Sub

非常感谢各位

Answer 1

我相信以下内容应该对您有所帮助，您应该尽量避免 Select & Activate 语句，还可以通过声明您的工作表您的代码更清晰，以查找我使用 Find 方法的字符串并将结果分配给 Range 变量以查看是否什么都找到了:

Sub OUTPUT()
Dim wsOut As Worksheet: Set wsOut = Sheets("OUTPUT")
Dim wsSF As Worksheet: Set wsSF = Sheets("SF Data")
'Declare and set the worksheets you are working with
Dim FoundPlease As Range
Dim LastRow As Long

    If wsOut.Range("A2").Value > 1 Then wsOut.Range("A2:I" & wsOut.Range("A1").End(xlDown).Row + 1).ClearContents
    'Clear contents if A2 > 1
    LastRow = wsSF.Cells(wsSF.Rows.Count, "I").End(xlUp).Row
    'find the last row with data on Column I in SF Data
    Set FoundPlease = wsSF.Range("I2:I" & LastRow).Find(What:="Please Review", LookAt:=xlWhole)
    'Search for "Please Review" on Column I in SF Data

    If FoundPlease Is Nothing Then 'if not found
        MsgBox "Nudda"
    Else 'if found
        wsSF.Cells.AutoFilter
        wsSF.Range("A1:I" & wsSF.Range("A1").End(xlDown).Row + 1).AutoFilter Field:=9, Criteria1:="Please Review"
        wsSF.Range("A2:I" & wsSF.Range("A1").End(xlDown).Row + 1).SpecialCells(xlCellTypeVisible).Copy
        wsOut.Range("A2").PasteSpecial xlPasteAll
        wsSF.Range("$A$1:$I$" & LastRow).AutoFilter Field:=9
    End If
    MsgBox "Sanity Check performed. " & Format(Now, "mmmm d, yyyy hh:mm AM/PM")
End Sub