CODEWITHSUNDEEP
vbaexcel
wilbev
wilbev

Reputation: 5991

Batch Find/Replace Function for Excel

So the attached VBA code works for what I'm looking for but it has one significant issue. It won't match the entire excel cell contents like I want it to. Instead if it will replace individual text within a cell instead of a cell by cell find and replace. Anyone have some tips how I can change to this to only replace text if the entire cell contents matches vs any text within the cell? 

Sub MultiFindReplace()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
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

Upvotes: 0

Views: 955

Answers (1)

Tim Williams
Tim Williams

Reputation: 166136

Add the argument

LookAt:=xlWhole

to the Replace

InputRng.Replace what:=Rng.Value, _
                 replacement:=Rng.Offset(0, 1).Value, _
                 LookAt:=xlWhole

Upvotes: 1

Related Questions