How to Find and Replace from List with Macro in Excel (5 Examples)

Example 1 – Find and Replace a Word from a List with Excel Macro

In the following dataset, we want to find the product Cable and replace it with TV.

STEPS:

• Right-click on the sheet name at the bottom.
• Select View Code.

• The VBA window will pop out with an empty code box.
• Copy the following code and paste it into the box.

Sub Replace_Word()
Range("B3:D8").Replace What:="Cable", Replacement:="TV"
End Sub

• Press F5. The Macros dialog box will pop out.
• Select Replace_Word and press Run.

• Close the VBA window and you’ll see the product TV in the places of Cable.

Read More: How to Find and Replace Using VBA

Example 2 – Apply Macro for Case-Sensitive Replacement from a List in Excel

The following dataset contains product Tv and we want it to replace it with TV.

STEPS:

• Right-click on the sheet name.
• Select View Code.
• Copy the given code and paste it into the code box that opened.

Sub Replace_CaseSensitive()
Range("B3:D8").Replace What:="Tv", Replacement:="TV", MatchCase:=True
End Sub

• Press F5. The Macros dialog box will pop out.
• Select Replace_CaseSensitive in the Macro Name and press Run.

• Close the VBA window and you’ll see the desired changes.

Example 3 – Use the Macro Replace Function for Finding and Replacing Text within a Text String

We want to replace Text present in a Text String. There are 3 text strings. We want to insert a space between the text MyMicrosoft.

STEPS:

• Right-click on the sheet name.
• Select View Code.
• Copy and paste the code given below in the code box.

Sub TextString_Replace()
UsedRange.Replace What:="MyMicrosoft", Replacement:="My Microsoft"
End Sub

• Press F5.
• Close the VBA window.

Example 4 – Find and Replace Multiple Values from a List with a Macro in Excel

The below dataset has a column Product and we want to replace the values in it with the column Replace with.

STEPS:

• Go to View Code.
• The VBA window will pop out and a code box will appear.
• Copy the below code and paste it into the box.

Sub Replace_MultiValues()
Dim R As Range
Dim InputR As Range, ReplaceR As Range
xTitleId = "Choose Range"
Set InputR = Application.Selection
Set InputR = Application.InputBox("Old ", xTitleId, InputR.Address, Type:=8)
Set ReplaceR = Application.InputBox("New :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each R In ReplaceR.Columns(1).Cells
    InputR.Replace What:=R.Value, Replacement:=R.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

• After that, close the VBA window.
• Now, select Macros from the Developer tab.

• The Macros dialog box will appear.
• Select Replace_MultiValues in the Macro name and press Run.

• The Choose Range dialog box will appear.
• Select $A$2:$A$6 in the Old box and press OK.

• Another dialog box will appear. Select $D$2:$E$6 in the New field and press OK.

• We’ll get the Product column according to our requirements.

Example 5 – Find and Replace Throughout the Entire Workbook in Excel with Macro

We have 2 sheets and we want to replace the product Cable present in both the sheets with product TV.

STEPS:

• Right-click on a sheet name.
• Select View Code.
• Copy the following code and paste it in the code box.

Sub FnD_All()
Dim sheet As Worksheet
Dim f As Variant
Dim r As Variant
f = "Cable"
r = "TV"
For Each sheet In ActiveWorkbook.Worksheets
  sheet.Cells.Replace what:=f, Replacement:=r, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next sheet
End Sub

• Press F5 and close the VBA window.
• The product Cable has been replaced by the product TV in all the worksheets.

Download the Practice Workbook

Related Articles

• Excel VBA: How to Find and Replace Text in Word Document
• Find and Replace a Text in a Range with Excel VBA
• Excel VBA to Replace Blank Cells with Text
• Excel VBA: Replace Character in String by Position
• Excel VBA: How to Replace Text in String