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.

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 — Tim Williams
– Tim Williams, Commented Oct 24, 2024 at 17:32
you could also do a very quick "CountIf" function and if equals 0, you can do a msgbox. — sous2817
– sous2817, Commented Oct 24, 2024 at 17:33

Tim Williams · Accepted Answer · 2025-01-23 16:31:39Z

4

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 Variant, 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

edited Jan 23 at 16:31

answered Oct 24, 2024 at 17:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Craig Coope Over a year ago

Thanks Tim. I read your inital comment before you edited it and I used range.replace=FALSE after changing the line to an IF and the code still worked but I never got the MsgBox when it found nothing to I guess you are right. Anyway, your code above works perfect. Thank you.

Tim Williams Over a year ago

Please flag as "Accepted" if this answered your question, to help out anyone coming along later with a similar problem.

Craig Coope Jan 22 at 22:39

Hi Tim. I hope you can help again. So I want the same outcome as above exactly but I've come to impliment it on my workbook and I'm stumped a bit because there are 3 sheets. The cFind cell is U3 on Sheet3 but I want it to perform the search and ClearContents on sheet1 Range A1:R34 AS WELL AS sheet2 Range A1:F45. Do I need to set up a sheet array as I 'm trying to have both sheet1 and sheet2 (a long with their cell range) in the "With" line. Something like: With Worksheets(Array(Sheet1.Range("A1:R34"), Sheet2.Range("A1:F45")))

Tim Williams Jan 22 at 22:40

So what about clearing the "find" value after a match is made (ie. cFind.ClearContents) - when should that happen? On the first match then stop looking, or ?

Craig Coope Jan 22 at 23:43

Hi. I want the find value to be cleared after all the instances of that search and replace have been completed. So the code needs to search the specified ranges on Sheet1 and Sheet2 and replace all instances of it with "" and then clear the "find" cell (U3). In simple terms, if I have "1234" written in 5 cells in sheet1 and 7 cells in sheet2, then I want all 12 cells (over both sheets) to have "1234" replaced by "" and the find cell is cleared ready to to input the next value to search and replace. Your code above works great. I just need it to include 2 ranges over 2 diff sheets, if possible.

|

Cyril · Accepted Answer · 2024-10-24 17:42:09Z

1

Starting a mock-up with the following expectations:

U3 is the lookup value
A1:R34 is the lookup range
Replace can be used for all instances

With sheets(1)
    dim lookUpRng as range:  Set lookUpRange = .Range("A1:R34")
    dim lookUpVal as range:  Set lookUpVal = .Range("U3") 'Treated as Range, but if you know if it's long/double/string then lookUpVal = .Range("U3").Value would be more approprate to not re-reference the cell and its property
    If Application.Countif(lookUpRng,lookUpVal.value) > 0 Then
        MsgBox "Value not found within A1:R34:  " & lookUpVal.value
    Else
        lookUpRng.Replace What:=Range("U3"), Replacement:="", Lookat:=xlWhole
    End If
End with

answered Oct 24, 2024 at 17:42

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Use MsgBox if Replace finds nothing to replace

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related