Cells.Find method with multiple "What" values

[KC Cat]

Hello to all,

I have a worksheet with IF statements that place different messages in cells if the user has not provided enough information, or incorrect information, etc. Here are the messages:

Check % Complete
Actual Missing
Remaining Missing
Zero Out Remaining
??
enter date
Qty?

I am trying to create a macro that will check the entire worksheet (that can be quite large) for instances of the values above. I want it to be very simple. The user will click a button, and the code will try to find the first instance of any of these "What" values and stop running for the user to address the issue. Then, they would simply click it again and again, until there are no more found, at which point, there will be a message stating "No incomplete inputs were found."

My problem is with the error handling aspects of this idea. If I only have one "What" value to find, I can do what I need to do without any help. It's the multiple "What" values aspect that is causing problems.

With the code below, it works when tested only under certain circumstances, but not all. I need to figure out a way for it to look for the first, and if it doesn't find any, go to the second, etc. If it finds one, the code should stop running with that instance activated for the user to address. If it finds none, it should tell the user that "No incomplete inputs were found."

Thanks in advance for considering my inquiry. For simplicity sake, the code below only includes the first to "What" values from above.

Sub Search_for_Incomplete_Inputs()

MsgBox "This search will attempt to find instances of the following: Check % Complete, Actual Missing, Remaining Missing, Zero Out Remaining, ??, enter date, Qty?" & vbNewLine & vbNewLine & "NOTE: You may have to execute this search multiple times until none are found.", vbInformation, "Incomplete Input Search"

On Error GoTo ErrorHandler:

Range("A4").Select

Cells.Find(What:="Check % Complete", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range("A4").Select

Cells.Find(What:="Actual Missing", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Err.Number = 1 Then
MsgBox "No incomplete inputs were found.", vbInformation, "Incomplete Input Search"
Else
Exit Sub
End If


ErrorHandler:
If Err.Number <> 0 Then
Err.Number = 1
End If

End Sub

 

[AlphaFrog]

Wow! Alpha is ... like, prescient!​

...or I had the same issue when I wrote my macro.