Macro to find if a word exists on a page

[tonywatsonhelp]

Hi Everyone,

So I need a macro that when run does this

Looks at the sheet("Raw") and checks if any cells contain the word "Empty"
now this will be the only word in the cell so we should check entire contents.
if it finds anything,
msgbox "You meed to deal with Empty's"
if not do nothing

thanks
Tony

 

[Joe4]

Here is one way:

Sub MyMacro()

    Dim lastCellAdd As String
    Dim rng As Range
    
'   Find last populated cell on sheet
    lastCellAdd = Range("A1").SpecialCells(xlLastCell).Address
    
'   Build range to check
    Set rng = Range("A1:" & lastCellAdd)
    
'   Check range for value of empty
    If Application.WorksheetFunction.CountIf(rng, "Empty") > 0 Then
        MsgBox "You need to deal with Empty's"
    End If

End Sub

 

[tonywatsonhelp]

Thank you Joe, worked first time

 

[Joe4]

You are welcome.