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

Sometimes, we have to find certain text or text strings or numerical values in our Excel worksheet and replace it with our desired input. We can do this manually, but that’s tiresome. The Excel Find & Select feature can perform the task. However, creating a VBA Macro with a simple code can also ease the operation. In this article, we’ll show you the effective ways to Find and Replace from List with Macro in Excel.

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

1. Find and Replace a Word from List with Excel Macro

In our first example, we’ll show how to replace a single word from a list with a Macro. In the following dataset, we want to find the product Cable and replace it with a TV. Therefore, follow the steps below to Find and Replace from List with Macro in Excel.

STEPS:

• First, select the sheet and right-click on the mouse.
• Then, select View Code.

• As a result, the VBA window will pop out and you’ll see a dialog box.
• Copy the following code and paste it into the dialog box.

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

• After that, press F5. The Macros dialog box will pop out.
• There, select Replace_Word and press Run.

• Finally, 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

2. Apply Macro for Case Sensitive Replacement from List in Excel

In the previous example, we’ve shown how to replace the word. But, the given code can’t distinguish between uppercase and lowercase letters. For example, the following dataset contains product Tv and we want it to replace it with TV. So, learn the below process for Case Sensitive Replacement from List in Excel.

STEPS:

• Firstly, select View Code.
• Hence, a dialog box will pop out in the VBA window.
• Now, copy the given code and paste it there.

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

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

• Lastly, close the VBA window and you’ll see the desired changes.

3. Use Macro Replace Function for Finding and Replacing Text within a Text String

Now, instead of a word, we want to replace Text present in a Text String. For instance, in the given dataset, there are 3 text strings. We want to insert a space between the text MyMicrosoft. Hence, follow the process below to perform the task.

STEPS:

• Select View Code at first.
• Consequently, a dialog box will appear in the VBA window.
• Then, copy and paste the code given below in the dialog box.

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

• Press F5.
• After that, close the VBA window.
• At last, it’ll return the text string making the changes.

4. Find and Replace Multiple Values from List with Macro in Excel

In this example, we’ll use a given list in our Excel worksheet to replace multiple values. The below dataset has a column Product and we want to replace the values in it with the column Replace with. Therefore, learn the steps given below to Find and Replace Multiple Values from a List with Macro in Excel.

STEPS:

• In the beginning, go to View Code.
• As a result, the VBA window will pop out and a dialog box will appear.
• Then, 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.

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

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

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

• Eventually, we’ll get the Product column according to our requirements.

5. Find and Replace All Throughout Entire Workbook in Excel with Macro

In our last example, we’ll show how to replace the target cell values present in all the sheets in a workbook with a VBA code. In the below dataset, we have 2 sheets and we want to replace the product Cable present in both the sheets with product TV. So, follow the below process to carry out the operation.

STEPS:

• Firstly, select View Code.
• The VBA window will pop out and a dialog box will appear.
• Next, copy the following code and paste it there.

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

• After that, press F5 and close the VBA window.
• In the end, you’ll see that the product Cable has been replaced by the product TV in all the worksheets.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to Find and Replace from List with Macro in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

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