Find and Replace using VBA

Hi,

I have inherited a macro that uses a Find and Replace to replace any instance of "#Missing", with a zero.

It uses cells.Select to select all the cells on the sheet.

It runs very quickly, but the only problem I encounter is that sometimes it throws up an Excel message (I have paraphrased it) saying that Excel cannot find any data to replace - this maybe because the worksheet is protected.

I know that I can use Application.displayalerts = false before the code, and = true after it to avoid the message, but I didn't want to do that as it would suppress any other alerts. I have tried capturing the Error number, but it returns zero. I couldn't find any other information on various forums apart from Application.displayalerts = false.

All I would like to do is to suppress this particular message.

Can anyone guide me on how I could achieve this, or indedd whether it is possible.

You could first do a find and trap that error:

Dim oFound as Range
On Error Resume Next
Set oFound=ActiveSheet.UsedRange.Find(ArgumentsGoHere)
If Err.Number<>0 Then
'Error occurred, No Match
End If

Hi,

Thank you for the answer, but I am still getting the message. Code is shown below:

Sub Missing()
Dim oFound As range
On Error Resume Next
Set oFound = ActiveSheet.UsedRange.Find("#Missing")
If Err.Number <> 0 Then
'Error occurred, No Match

msgbox Err.number & " " Err.description

End If

application.ScreenUpdating = False
   
    Cells.Select
    selection.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
  

application.ScreenUpdating = True
   
range("A1").Select
End Sub 

I had thought of skipping through each cell (because I can avoid the message that way), but that takes too long.

Do you have any suggestions as to anything else I could try?

Check your VBA settings.

Tools, Options, General tab. The Error handling option at middle right should be set to "Break on unhandled errors", not "Break on all errors"

Hi,

Settings are as you suggested - Message is still occurring. 

Do this like this:

Sub Missing()
Dim oFound As range
On Error Resume Next
Set oFound = ActiveSheet.UsedRange.Find("#Missing")
If Err.Number <> 0 Then
'Error occurred, No Match
Else
application.ScreenUpdating = False
Cells.Select selection.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByColumns, MatchCase:=False
application.ScreenUpdating = True range("A1").Select
End If
End Sub

Jan Karel,

Thanks for your response.

Initially it didn't work.

I added

Dim x as string

and then added a further line under Set oFound =.....

x = oFound

and it worked perfectly.

Not entirely sure why this would be, but thanks anyway.

Hi,

I have a similar problem to the original question but I need to replace multiple items within just one column. If I do not find an item to replace then I want to move onto the next?

Find                              Replace

I Know I could use the suggestion from Jan (above) but I did not want to replicate the code for each of the things to replace. Can anybody help?

I have answered my own question with the following code (it is not perfect but it worked for me):

Sub ReplaceStatus()
Dim v As Variant, w As Variant
Dim i As Variant, findstatus As Variant, ReplaceStatus As Variant
Dim oFound As Range

v = Array("A", "B", "C", "D")

w = Array("100 - A", "110 - B", "120 - C", _
         "130 - D")
        
Application.ScreenUpdating = False

For i = LBound(v) To UBound(v)
    findstatus = v(i)
    ReplaceStatus = w(i)
  
    Set oFound = ActiveSheet.UsedRange.Find(findstatus)
   
    If Not oFound Is Nothing Then

        Cells.Select
        Selection.Replace What:=findstatus, Replacement:=ReplaceStatus, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False

        Range("A1").Select
    End If
Next i

Application.ScreenUpdating = True

End Sub

Sorry to be coming in late, but I would have solved this just using qorksheet functions, namely VLOOKUP.

Somewhere on the sheet create a table like this:

A   110 - A
B   120 - B
C   130 - C
D   140 - D

Then in a cell adjacent to the cell you wish to replace, enter a vlookup formula along the lines of:
  =vlookup(A2, $G$1:$H$4, 2, false)
then if you really want, you can pastevalues the result over the original cell, if you relaly want it 'in place'

Or, you could write code to evaluate the vlookup and assign it to the cell.value.

range("a2").value = worksheetfunction.vlookup(range("a2").value, range("$G$1:$H$4"), 2, false)

oh, you would also have a check for when the value of a2 isn't in the lookup table, and to just not change the contents of the cell. So something like:

temp = worksheetfunction.vlookup(range("a2").value, range("$G$1:$H$4"), 2, false)
if not iserror(temp) then range("a2").value = temp

Cheers,

Rick

Rick,

Thank you for the reponse and great hint. I can see that my routine was a little over kill and not an easy one to maintain.

Cheers.