What is Find & Replace Function in VBA Excel?
In VBA, the Find and Replace or Replace function refers to a feature that allows you to search for specific text or values within a range, such as a worksheet in Excel, and replace them with other text or values. This functionality is commonly used when automating tasks in Microsoft Office applications like Excel and Word.
The Find and Replace function in VBA is typically used with the Find method and the Replace method, both of which are applied to a range or object.
Let’s observe an example below to know how VBA Find and Replace works.
We need to replace Tomato with Watermelon. We can utilize VBA Find and Replace in String, as shown below.
Here, we create a subroutine to check through the range “A1:A10” for “Tomato” and change it to “Watermelon.” It will result in the output shown below.
Table of contents
Key Takeaways
- The VBA Find and Replace function replaces occurrences of a substring within a string or a cell value.
- It takes three mandatory arguments: the string or cell value to search in, the substring to find, and the substring to replace it with.
- Optionally, you can use two more arguments to specify the start position and the number of replacements to make.
- The function is not case-sensitive by default, but you can implement case-sensitive replacements with additional logic.
- It is commonly used for data manipulation and text processing tasks in VBA, including Excel.
VBA Find and Replace Syntax
The formula is:
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
Where,
expression: This is required and represents a range or object where you want to perform the replace operation.
What: This is required and specifies the text or value you want to find and replace.
Replacement: This is required and specifies the text or value with which you want to replace the found text.
LookAt: This optional parameter specifies how to look for the text. It can take the following values:
- xlWhole: Match the entire cell contents.
- xlPart: Match any part of the cell contents.
- SearchOrder: This optional parameter specifies the order in which to search. It can take the following values:
- xlByRows: Search by rows.
- xlByColumns: Search by columns.
- MatchCase: This optional parameter specifies whether to perform a case-sensitive search. It can be True or False.
MatchByte: This is an optional parameter used in international versions of Excel.
SearchFormat: This is an optional parameter used to search for cell formatting.
ReplaceFormat: This is an optional parameter used to apply formatting to the replaced text.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to use VBA Find and Replace?
Follow the steps below to view how to perform VBA Find and Replace in Range.
Step 1: Once you open the Excel Workbook, choose the “Developer” icon in the title bar and click on it.
After you click the Developer icon, select “Visual Basic”. It opens the VBA window where you can start coding.
It opens the VBA editor.
In the VBA editor, click the “Insert” button in the title bar and select the “Module” option.
You are ready to learn how to apply VBA Find and Replace in your code.
Let’s see how to implement VBA Find and Replace text in Formula. See the table below:
Step 2: To convert the string “XYZ” to “Hello World” using VBA Find and Replace, start by creating a subroutine and follow the subsequent steps.
Step 3: Declare a variable as a VBA Range datatype to store the current range.
Step 4: Assign the range of the table above to the variable declared earlier.
Step 5: Initialize the VBA Find and Replace function, where you describe the parameters to replace a given string in the column.
We specify the variable to be searched and its replacement word with the predefined parameters:
- What
- Replacement
In this case, the case sensitivity is declared “False” by default. Hence, irrespective of the case the string is in, all will be changed.
Code:
Sub FindAndReplaceExample()
Dim rng As Range
Set rng = Range(“A1:A10”)
rng.Replace What:=”XYZ”, Replacement:=”Hello World”
End Sub
Step 6: Press the green arrow button on the VBA Toolbar.
Then, run the subroutine and go to the worksheet to view your table.
You have learned how to use VBA Find and Replace in string. Let us look at some more examples in detail.
Examples of VBA Find and Replace in Excel
You can see examples of the various use cases of VBA Find and Replace in Excel below.
Example #1
Consider you have a table of the same word but in different cases. You want to change all the words in the table regardless of their cases.
It can be done using the VBA Find and Replace function.
Step 1: Define a subroutine to Find and Replace a string in a given column in Excel.
Step 2: Define two variables, to save the worksheet name in which this subroutine will work on.
Step 3: Set the worksheet you’ll be working on.
Step 4: Start with defining the range of the table; here, we found the last non-empty cell in the row using xlUp in Excel VBA by taking column A as a comparison.
Step 5: Call the replace function in the range and search for “Lorem Ipsum.” It is case insensitive. Hence, MatchCase is defined as “False.”
Code:
Sub ReplaceFunctionExample()
Dim ws As Worksheet
Dim replaceRange As Range
Set ws = ThisWorkbook.Worksheets(“Sheet2”)
Set replaceRange = ws.Range(“A1:A” & ws.Cells(ws.Rows.count, “A”).End(xlUp).Row)
replaceRange.Replace What:=”lorem ipsum”, Replacement:=”What’s Up?”, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub
Step 6: Press “F5” to run the code. The output is displayed as shown below:
In this case, we set up VBA Find and Replace Text in the column with matching case insensitivity as False. But what happens if we need to match case insensitivity? To do so, go through the next example.
Example #2
Go through the table below. Here, we want to change the word “Crypto” to “Money”. In this case, there are multiple cases of the same word. Since we want only to change the word “Crypto,” we’ll need to perform VBA Find and Replace in String in the formula with case sensitivity.
Step 1: For changing “Crypto” to “Money,” we need to declare a sub-procedure.
Step 2: Define the worksheet and the range this sub-procedure will work on.
Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus seven days.
Step 4: Initialize the input date as a Date variable, the number of hours and days to add as integers, respectively, and a new date variable to store the date value after it’s been added.
Step 5: Set the sheet number on which the sub-procedure will work.
Step 6: Define the range of the cells by defining the number of rows. To deal with additional rows in the future, we can use the VBA XlUp button, which imitates the up arrow key in the keyboard.
xlUp checks for the last value that’s not empty in a cell and returns an integer value.
Step 7: Call the VBA Find and Replace function with the search word and the replacement word defined. Then, define MatchCase as “True”.
This will only Replace the word with the case “Crypto” and everything else will be left untouched.
Code:
Sub ReplaceFunctionCaseSensitive()
Dim rng As Range
Dim w As Worksheet
Set w = ThisWorkbook.Worksheets(“Sheet4”)
Set rng = w.Range(“A1:A” & w.Cells(w.Rows.count, “A”).End(xlUp).Row)
rng.Replace What:=”Crypto”, Replacement:=”Money”, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True
End Sub
Step 8: Run the above code in the VBA Editor. Then go back to the sheet to view the changes.
Important Things To Note
- By default, the Replace function is not case-sensitive. It will replace all occurrences of the search string regardless of their case.
- The Replace function modifies the original data in the cell or string it operates on. If you need to keep a record of the original data, create a backup or copy of the data before performing replacements.
- The Replace function can be used with both strings and cell values in Excel.
- If you perform replacements in an Excel worksheet, iterate through the range properly using a loop to apply the replacement to each cell individually.
- Double-check that you are using the correct parameters when calling the Replace function, such as the search string, replacement string, and the string you are operating on. Ensure that you specify these parameters in the correct order.
- Before running VBA code that modifies your workbook’s data, especially when performing extensive replacements, saving a backup copy of your workbook is a good practice.
Frequently Asked Questions (FAQs)
To find and replace wildcards in Excel VBA,
• You can employ the Like operator with an asterisk (*) as a wildcard character when using wildcards in VBA.
• You should specify both the search pattern you’re looking for and the replacement you want to apply.
• To perform these replacements throughout a range, you need to iterate through each cell or element in that range.
• The Replace function in VBA is used to execute the actual search and replace operations.
• It’s crucial to place wildcards correctly within your search pattern to achieve the desired results.
Open the workbook where you want to perform the Find and Replace operations. Iterate through all the worksheets within the workbook to ensure you cover all the sheets. Within each worksheet, you’ll need to loop through each cell or range where you intend to perform Find and Replace. Utilize the Replace method in VBA to locate and replace the desired content within the cells or ranges.
1. To use VBA Find and Replace character in string, start by defining the string you want to perform the find and replace operation.
2. You can utilize the Replace function in VBA to execute the replacement task within the specified string. Then, specify the character you want to find within the string. Indicate the character with which you want to replace the found character in the string.
3. After completing the replacement, you can choose to store the resulting modified string in a new variable or overwrite the original string with the updated version
Download Template
This article must be helpful to understand the VBA Find and Replace, with its syntax and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Find and Replace. We learn how to perform find and replace function in range, with examples and points to remember. You can learn more from the following articles –
Leave a Reply