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.

Find and Replace a Word from List with Excel Macro

STEPS:

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

Find and Replace a Word from List with Excel Macro

  • 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

Find and Replace a Word from List with Excel Macro

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

Apply Macro for Case Sensitive Replacement from List in Excel

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

Apply Macro for Case Sensitive Replacement from List in Excel

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

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

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

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

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

Find and Replace Multiple Values from List with Macro in Excel

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

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

  • 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

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

Find and Replace All Throughout Entire Workbook in Excel with Macro

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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

7 Comments
  1. This is helpful, but missing the last example, which would naturally be using VBA to Find and Replace Multiple Values from List All Throughout Entire Workbook in Excel.

    • Thank you Michael for your comment. We made adjustments in the Excel file. Please find the added VBA code for the last example you mentioned.
      Regards,
      Aung

  2. where is using VBA to Find and Replace Multiple Values from List All Throughout Entire Workbook in Excel.

    • Hello Ahmed,

      Thanks for your question! The section you’re referring to is covered in Example 5: Find and Replace from List in Entire Workbook Using VBA. It explains how to loop through all worksheets and apply the replacement logic across the entire workbook. Let me know if you’d like a simplified version of the code or any clarification!

      Regards
      ExcelDemy

      • example 5 replace one word with another i need to replace multiple values from a list can you help please

        • Avatar photo
          Shamima Sultana Apr 24, 2025 at 12:42 AM

          Hello Ahmed,

          Thank you for the clarification! You’re absolutely right, Example 5 demonstrates replacing one value across the entire workbook, while Example 4 shows how to replace multiple values but only within a single sheet.

          To achieve what you’re asking—replacing multiple values from a list across the entire workbook—you can combine the logic from both examples. Here’s a simple version of the code that does exactly that:

          
          Sub ReplaceMultipleValuesWorkbook()
              Dim ws As Worksheet
              Dim lookupRange As Range
              Dim cell As Range
              Dim findValue As Variant, replaceValue As Variant
              Dim i As Long
          
              ' Set your lookup table range (assumes it's on Sheet1, columns A and B)
              Set lookupRange = Worksheets("Sheet1").Range("A2:B10") ' Adjust range as needed
          
              ' Loop through each sheet in the workbook
              For Each ws In ThisWorkbook.Worksheets
                  If ws.Name <> "Sheet1" Then ' Skip the sheet with the lookup table
                      For Each cell In ws.UsedRange
                          For i = 1 To lookupRange.Rows.Count
                              findValue = lookupRange.Cells(i, 1).Value
                              replaceValue = lookupRange.Cells(i, 2).Value
                              If cell.Value = findValue Then
                                  cell.Value = replaceValue
                              End If
                          Next i
                      Next cell
                  End If
              Next ws
          End Sub

          What it does:
          1. Looks for a list of values in Sheet1!A2:B10 (column A = find, column B = replace).
          2. Applies replacements in all other worksheets.

          Let me know if you need help customizing the lookup range or skipping certain sheets!

          Regards
          ExcelDemy

  3. i need a simple example . example 5 find and replace one value .i need to find and replace multiple values as in example 4 but for the whole work book if you can help please

Leave a reply

ExcelDemy
Logo

Advanced Excel Exercises with Solutions PDF