Excel VBA Looking for Text in a Whole Column

Question

I have the following code to find the phrase "Please Review" in Column "I" and if not found show message, if found it must run the rest of my code but its not liking my IF code:

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

Many thanks guys

Xabier · Accepted Answer · 2018-07-20T08:06:47.417

I believe the following should help you out, you should try to avoid Select & Activate statements, also by declaring your worksheets your code is more legible, to find the string I used the Find method and allocated the result to a Range variable to see if anything was found:

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

score 1 · Answer 2 · answered Jul 20 '18 at 07:59

It is giving an error, because Range("I2:I192754").Value <> "Please Review" is a bit illegal. If you want to check whether in one of the cells in the range there is the string "Please Review" present, you may consider using =CountIf() function:

Sub TestMe()

    If WorksheetFunction.CountIf(Range("I2:I192754"), "Please Review") > 0 Then
        Debug.Print WorksheetFunction.CountIf(Range("I2:I192754"), "Please Review")
    End If

End Sub

Later, you may take a look at this topic - How to avoid using Select in Excel VBA.

score 1 · Answer 3 · answered Jul 20 '18 at 08:02

One way would be to check each cell individually:

Dim TextFound As Boolean

TextFound=False

For Each cell In Range("I2:I192754")
    If cell.value="Please Review" Then
        TextFound=True
        Exit For
    End If
Next

If TextFound Then
    ...
Else
    ...
End IF

Excel VBA Looking for Text in a Whole Column

3 Answers3