Excel VBA: Use MsgBox if Replace finds nothing to replace

Question

I'm using a simple code to find values in a range and if that value is found, all instances of it are cleared with a "".

The code:

Sub Clear()
    Range("A1:R34").Replace What:=Range("U3"), Replacement:="", Lookat:=xlWhole
    Range("U3").ClearContents
End Sub

So if the value typed into U3 is found within the range then those cells that contain that value are cleared. This code works fine and those instances are cleared if found, however I would like a dialogue box to appear If the value in U3 is NOT found in my range. So I would use MsgBox "Invalid Order Number". I can't figure out how to place this into my code. I feel I need an IF and Then but I'm a novice with this so not sure.

Basically I want the code to be like this:

Sub Clear()

    Range("A1:R34").Replace What:=Range("U3"), Replacement:="", Lookat:=xlWhole
    'If (if the above Replace finds nothing) Then MsgBox "Invalid Order Number"

    Range("U3").ClearContents

I don't know the syntax to get this to work.

Answer 1

Docs for Range.Replace suggest it returns a Boolean indicating whether any matches were found, but in my testing it always returns True. One workaround is to use Find() to see if there's any match, then use Replace if the return value from Find() is not Nothing

Sub Clear()
    Dim cFind as Range, ws as worksheet

    Set ws = ActiveSheet 'or specific named sheet
    Set cFind = ws.Range("U3")
    With ws.Range("A1:R34")
        If Not .Find(What:=cFind.Value, LookAt:=xlWhole) Is Nothing Then
            .Replace What:=cFind.Value, Replacement:="", Lookat:=xlWhole
            cFind.ClearContents
        Else
            Msgbox "Invalid order number: " & cFind.Value
        End If
    End With
End Sub

EDIT: multiple search ranges

Sub SearchAndClearRanges()
    
    Dim cFind As Range, wb As Workbook, n As Long, rng As Range, v
    
    Set wb = ThisWorkbook
    
    Set cFind = wb.Worksheets("Sheet3").Range("U3")
    v = cFind.Value
    
    For Each rng In Array(wb.Worksheets("Sheet1").Range("A1:R34"), _
                          wb.Worksheets("Sheet2").Range("A1:F45"))
        
        If Not rng.Find(What:=v, Lookat:=xlWhole) Is Nothing Then
            rng.Replace What:=v, Replacement:="", Lookat:=xlWhole
            n = n + 1 'increment "found" count
        End If
    Next rng
    
    If n > 0 Then 'found on any sheet?
        cFind.ClearContents
    Else
        MsgBox "Invalid order number: " & v 'was not found
    End If
    
End Sub