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]

I assume the user is correcting the "Incomplete Inputs" in between button clicks. Otherwise the macro just keeps finding the same uncorrected messages over and over.

Sub Search_for_Incomplete_Inputs()

    Dim FindWhat As Variant, Found As Range
    Static LastFound As String, i As Integer
    
    If LastFound = vbNullString Then
        MsgBox "This search will attempt to find instances of the following:" & vbCr & _
               "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"
    End If
        
    FindWhat = Array("Check % Complete", "Actual Missing", "Remaining Missing", "Zero Out Remaining", "~?~?", "enter date", "Qty~?")
    
    For i = i To UBound(FindWhat)
    
        If LastFound = vbNullString Then LastFound = Cells(Rows.Count, Columns.Count).Address
        
        Set Found = Cells.Find(What:=FindWhat(i), After:=Range(LastFound), LookIn:=xlValues, _
                               LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False, SearchFormat:=False)
                              
        If Not Found Is Nothing Then
            LastFound = Found.Address
            Found.Select
            Exit Sub
        End If
        
    Next i
    
    LastFound = vbNullString
    i = 0
    MsgBox "No incomplete inputs were found.", vbInformation, "Incomplete Input Search"
    
End Sub

It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

 

[shg]

Try this:

Sub Search_for_Incomplete_Inputs()
    Dim vWhat       As Variant
 
    MsgBox Prompt:="... long message ...", _
           Title:="Incomplete Input Search"
    On Error Resume Next
 
    For Each vWhat In Array("Check % Complete", "Actual Missing")
        Err.Clear
        Cells.Find(What:=vWhat, _
                   After:=Range("A4"), _
                   LookIn:=xlValues, _
                   LookAt:=xlPart, _
                   SearchOrder:=xlByRows, _
                   SearchDirection:=xlNext, _
                   MatchCase:=False, _
                   SearchFormat:=False).Activate
        
        If Err.Number = 0 Then
            MsgBox Prompt:="Fix this and run again, please", _
                   Title:="Incomplete Input Search"
            Exit Sub
        End If
    Next vWhat
    
    MsgBox Prompt:="Ta da! Golden!", _
           Title:="Incomplete Input Search Complete"
End Sub

 

[KC Cat]

Alpha Frog & shg,

Thanks to both of you for replying!

I have tried both ideas, and there seems to be some kind of flaw. If you open a blank sheet and enter some sort of text in a cell (other than the messages I'm looking for) and run either code it will find this text. It shouldn't!

I don't understand.

Please let me know what is happening. Thanks!

 

[AlphaFrog]

If you changed the code from my original above, consider this...

If you have within the FindWhat array as one of the strings to search for is "??", the question mark is a wild card in the .Find method. So if you are searching for "??", you will find any cell with two or more characters. To search for a literal question mark, you have to precede it with a tilde for each. e.g. "~?~?". That will find two literal question marks. The same applies to "Qty~?"

Could that be the problem?

 

[shg]

I am going to be very impressed if that's correct, Alpha. Like ... out of thin air!

 

[KC Cat]

Alpha Frog,

Thanks, that took care of it!!

 

[KC Cat]

And, just to settle the score between you two, I went with shg's code, as it seems a bit "cleaner", and has the messages as I wanted.

Thanks to both - you both helped!!

 

[AlphaFrog]

1. Glad it worked.
2. shg's is cleaner. Good choice.
3. We don't keep score.

 

[shg]

Thanks, that took care of it!!

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