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

Below is a dataset that contains information about the departments of employees. We will try to use VBA to find and replace the department’s values.

Method 1 – Using VBA to Find and Replace Multiple Values in Excel

STEPS:

• Go to the Developer tab and select Visual Basic.
• The Visual Basic window will appear. You can also do it by pressing Alt + F8.

• Right-click on the sheet you want to apply the VBA.
• A drop-down menu will open.
• Select View Code from there. It will open the Code window.

• Enter the code in the Code window:

Sub Find_and_Replace()
Dim xrng As Range
Dim InRg As Range
Dim Reprng As Range
Title = "Find and Replace Values"
Set InRg = Application.Selection
Set InRg = Application.InputBox("Find Values: ", Title, InRg.Address, Type:=8)
Set Reprng = Application.InputBox("Replace with: ", Title, Type:=8)
Application.ScreenUpdating = False
For Each xrng In Reprng.Columns(1).Cells
    InRg.Replace What:=xrng.Value, Replacement:=xrng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

Here, xrng is the range where we need to store the replaced values. InRg is the range where we need to find the values and Reprng is the range that we need to replace with the values.

• Press Ctrl + S to save the code.
• Close the Visual Basic window.
• Select Macros from the Developer tab. The Macro window will appear.

• Select the desired code from the Macro dialog box and Run it.

• A Find and Replace Values window will open.
• Select the cells where you must perform the Find & Replace operation.
• Click OK. Here, we have selected Cell C5 to Cell C10.

• A dialog box will appear.
• Select the values that we need to replace with. Here, we have selected Cell E5 to Cell F6.
• Click OK to proceed.

You will see the results below.

Method 2 – Applying VBA to Find Multiple Values and Replace Them with a Single Value in Excel

STEPS:

• Go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• Double-click on the sheet where you want to apply the VBA.
• This will open the Code window.
• Enter the code in the Code window:

Sub Replace_with_Single_Value()
Dim sheet As Worksheet
Dim MultiValues As Variant
Dim SingleValue As String
Dim y As Long
MultiValues = Array("Marketing", "Accounting")
SingleValue = "Sales"
For y = LBound(MultiValues) To UBound(MultiValues)
For Each sheet In ActiveWorkbook.Worksheets
sheet.Cells.Replace What:=MultiValues(y), Replacement:=SingleValue, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sheet
Next y
End Sub

Here, we have written the values we need to find in the MultiValues variable and the ones we need to replace in the SingleValues variable. To use the code correctly, you can change the values according to your dataset.

• Save it by pressing Ctrl + S.
• Close the Visual Basic window.
• Go to the Developer tab and select Macros.

• Select the code and Run it from the Macro window.

You will see the following results: All the values in the Department column are replaced with Sales.

Method 3 – Using Excel VBA to Find and Replace Multiple Values with User-defined Function

STEPS:

• Select Visual Basic from the Developer tab.
• The Visual Basic window will appear.

• Select Insert and select Module from the drop-down menu.
• Enter the code below in the Module window:

Function FINDandREPLACE(Inrng As Range, Fndrng As Range, Reprng As Range) _
As Variant()
  Dim Resultar() As Variant
  Dim SrchRepar(), Temp As String
  Dim FndCRindex, CountFndR As Long
  Dim InCRindex, InCCindex, CountIR, CountIC As Long
  CountIR = Inrng.Rows.Count
  CountIC = Inrng.Columns.Count
  CountFndR = Fndrng.Rows.Count
  ReDim Resultar(1 To CountIR, 1 To CountIC)
  ReDim SrchRepar(1 To CountFndR, 1 To 2)
  For FndCRindex = 1 To CountFndR
    SrchRepar(FndCRindex, 1) = Fndrng.Cells(FndCRindex, 1).Value
    SrchRepar(FndCRindex, 2) = Reprng.Cells(FndCRindex, 1).Value
  Next
  For InCRindex = 1 To CountIR
    For InCCindex = 1 To CountIC
    Temp = Inrng.Cells(InCRindex, InCCindex).Value
    For FndCRindex = 1 To CountFndR
    Temp = Replace(Temp, SrchRepar(FndCRindex, 1), SrchRepar(FndCRindex, 2))
    Next
    Resultar(InCRindex, InCCindex) = Temp
    Next
  Next
  FINDandREPLACE = Resultar
End Function

Here, the Resultar() is the array where we need to store the results, the SrchRepar is the array where we need to store the find and replace pairs. The Temp stores the temporary strings. FndCRindex is the index of the current row of the SrchRepar array and CountFndR is the count of the rows. InCRindex and InCCindex are the input current row and column index respectively. CountIR and CountIC are the counts of the input row and column.

• Press Ctrl + S to save the code and close the Visual Basic window.
• Create a Helper column like the picture below.

• Select Cell D5 and enter the formula:

Here, the first argument denotes the range where we need to find values, the second argument denotes the values we need to find and the third argument represents the value we need to replace with.

• Press Enter to see results like the one below in the Helper column.

• Copy the values of the Helper column.

• Paste only the values in the Department column and delete the Helper column to see the results below.

Related Content: Find and Replace a Text in a Range with Excel VBA

Things to Remember

We need to remember certain things when we are using the above VBA codes.

• In Method-2, the code will replace all the values of your workbook. To apply in a certain worksheet, use Method-1.
• In Method-3, we need to copy the values from the Helper and paste the values only.

Download the Practice Book

Download the practice book here.

Related Articles

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