Find and Replace Multiple values in all worksheets

[Average Joe]

Hi,
Does anybody have any suggestions / alternatives to modify the below code to allow find and replace multiple values on all worksheets?
Also, if there is a way to fix replacing range, rather than having to select it each time?

Thanks

Sub MultiFindNReplaceNew()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "Test"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

What is the range of your replacement values?
Will your InputRng vary?, or can that be set?
To replace over all worksheets, will the input & replace ranges be the same on every sheet?

 

[Average Joe]

I would like to have a separate worksheet with the InputRng fixed in columns A & B.
The range of the replacement values would then be the full range of all the remaining worksheets, if possible.

If I didn't do it this way, I think the macro would overwrite the values in the InputRng aswell?

Thanks for your help.

 

[Fluff]

Untested but try

Sub ReplaceInAllShts()
'Average Joe

    Dim Cnt As Long
    Dim Replc As Variant
    Dim Sht As Long
  
    With Sheets(1)
        Replc = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End With
    
    For Sht = 2 To Worksheets.Count
        For Cnt = 1 To UBound(Replc)
            Sheets(Sht).UsedRange.Replace what:=Replc(Cnt, 1), Replacement:=Replc(Cnt, 2), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next Cnt
    Next Sht
    
End Sub

This is based on your first sheet (ie Sheets(1)) containing your search & replace data.

 

[Average Joe]

Unfortunately doesn't seem to be working - Run-time error 9?

 

[Fluff]

What line of code did it fail on?

 

[Average Joe]

Looks like it failed on Line 14, but the whole section below was highlighted:

            Sheets(Sht).UsedRange.Replace what:=Replc(Cnt, 1), Replacement:=Replc(Cnt, 2), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False

 

[Fluff]

Try this

Sub ReplaceInAllShts()
'Average Joe

    Dim Cnt As Long
    Dim Replc As Variant
    Dim Sht As Long
  
    With Sheets(1)
        Replc = .Range("A1", .Range("B" & Rows.Count).End(xlUp)).Value
    End With
    
    For Sht = 2 To Worksheets.Count
        For Cnt = 1 To UBound(Replc)
            Sheets(Sht).UsedRange.Replace what:=Replc(Cnt, 1), Replacement:=Replc(Cnt, 2), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next Cnt
    Next Sht
    
End Sub

 

[Average Joe]

This is working, but only when the replacing value is the exactly the same as the cell contents - it will not change part of a cell or file path.

 

[Fluff]

In that case change
LookAt:=xlWhole to LookAt:=xlpart