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

Sometimes, we have to find certain text or text string 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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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

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.

Find and Replace a Word from List with Excel Macro

STEPS:

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

Find and Replace a Word from List with Excel Macro

  • 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

Find and Replace a Word from List with Excel Macro

  • 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 Formula in Excel (4 Examples)


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.

Apply Macro 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

Apply Macro for Case Sensitive Replacement from List in Excel

  • 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.

Read More: How to Replace Text in Excel Formula (7 Easy Ways)


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.

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

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

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

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

Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)


Similar Readings:


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.

Find and Replace Multiple Values from 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

Find and Replace Multiple Values from List with Macro in Excel

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

Find and Replace Multiple Values from List with Macro in Excel

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

Find and Replace Multiple Values from List with Macro in Excel

  • 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.

Read More: How to Find and Replace Multiple Values in Excel with VBA (3 Examples)


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.

Find and Replace All Throughout Entire Workbook in Excel with Macro

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.

Related Content: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)


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

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo