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