Find and Replace Multiple Values in Excel

This post will guide you how to find and replace multiple values at once with VBA macro in Excel. How do I make multiple find and replace in Excel.

Find And Replace Multiple Values in Excel

Assuming that you have a list of data in range B1:B6, and you want to find multiple values and replace those value with different values. For example, find “excel” string and replace its as excel2013, and find “word’’ string and replace its as word2013, and so on. How to achieve it. You should use an excel VBA macro to quickly find and replace multiple values. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub ReplaceMulValues()
    Dim myRange As Range, myList As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one range to be searched", "Find And Replace Multiple Values", myRange.Address, Type:=8)
    Set myList = Application.InputBox("select two column range where find/replace pairs are:", "Find And Replace Multiple Values", Type:=8)
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one range to be searched

#7 select two column range where find/replace pairs are

#8 let’s see the result.