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

In this article, we will learn to find and replace multiple values in excel with VBA. VBA stands for Visual Basic for Applications. It is a programming language for Microsoft Excel. We can use VBA to perform various tasks. In Excel, we often need to find and replace multiple values to update our dataset. Of course, we can do it manually. But, today, we will demonstrate 3 examples to find and replace multiple values in excel with VBA.

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

To explain the examples, we will use a dataset that contains information about the Department of some employees. We will try to use VBA to find and replace the values of the Department.

1. VBA to Find and Replace Multiple Values in Excel

In the first example, we will use VBA to find and replace the name of the departments. Here, we will find Marketing and Accounting in the Department column. And then, replace them with Sales and Finance respectively.

Let’s follow the steps below to learn the first example.

STEPS:

• In the beginning, go to the Developer tab and select Visual Basic. The Visual Basic window will appear. You can also do it by pressing Alt + F8 from the keyboard.

• Secondly, right-click on the sheet you want to apply the VBA. A drop-down menu will occur.
• Thirdly, select View Code from there. It will open the Code window.

• Now, type 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.
• After that, close the Visual Basic window.
• Next, select Macros from the Developer tab. The Macro window will appear.

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

• After running the code, a Find and Replace Values window will appear.
• Select the cells where you need to perform the Find & Replace operation and click OK. Here, we have selected Cell C5 to Cell C10.

• After clicking OK, another dialog box will appear.
• In this case, select the values that we need to replace with. Here, we have selected Cell E5 to Cell F6.
• Click OK to proceed.

• Finally, you will see results like below.

2. Apply VBA to Find Multiple Values and Replace Them with Single Value in Excel

In the second example, we will find multiple values and replace them with a single value with VBA. The previous dataset will be used here. Here, we will replace Marketing and Accounting with Sales.

Let’s pay attention to the steps below to know more.

STEPS:

• In the first place, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• After that, double-click on the sheet where you want to apply the VBA.
• This will open the Code window.
• Now, type 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 that we need to find in the MultiValues variable and the values we need to replace in the SingleValues variable. You can change the values according to your dataset to use the code correctly.

• After typing the code, save it by pressing Ctrl + S.
• Then, close the Visual Basic window.
• Next, go to the Developer tab and select Macros.

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

• Finally, after running the code, you will see results like below. All the values in the Department column are replaced with Sales.

3. Excel VBA to Find and Replace Multiple Values with User Defined Function

In the last example, we will create a user-defined function with VBA to find and replace multiple values in excel. Again, we will use the same dataset.

Let’s observe the steps below to learn this example.

STEPS:

• First of all, select Visual Basic from the Developer tab.
• The Visual Basic window will appear.

• Secondly, select Insert and select Module from the drop-down menu.
• Now, type 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.
• After that, create a Helper column like the picture below.

• Next, select Cell D5 and type 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.

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

• After that, copy the values of the Helper column.

• Finally, Paste Only the Values in the Department column and Delete the Helper column to see results like 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 Practice Book

Download the practice book here.

Conclusion

We have discussed 3 examples to find and replace multiple values in Excel with VBA. I hope these methods will help you to solve problems regarding finding and replacing. Furthermore, we also have the practice book at the beginning of the article. You can also download it to practice more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

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