Table of Contents
Find and Replace in VBA is a handy feature available in the Visual Basic for Applications (VBA) programming language that allows you to quickly find and replace text or data in an entire document, worksheet, or range of cells. It can be accessed through the Excel VBA editor (Alt+F11) or through the Ribbon menu. There are several ways to use Find and Replace in VBA, as well as different parameters that can be used to customize the search. Examples of how to use Find and Replace in VBA include searching for text, searching with wildcards, and using parameters for more advanced searches.
You can use the following methods to find and replace specific strings in a range using VBA:
Method 1: Find and Replace Strings (Case-Insensitive)
Sub FindReplace()
Range("A1:B10").Replace What:="Mavs", Replacement:="Mavericks"
End Sub
This particular macro will replace each occurrence of “Mavs” with “Mavericks” in the range A1:B10.
Method 2: Find and Replace Strings (Case-Sensitive)
Sub FindReplace()
Range("A1:B10").Replace What:="Mavs", Replacement:="Mavericks", MatchCase:=True
End Sub
This particular macro will replace each occurrence of “Mavs” with “Mavericks” in the range A1:B10 only if the case matches.
For example, the string “mavs” would not be replaced because it doesn’t match the case of “Mavs” precisely.
The following examples show how to use this method in practice with the following dataset in Excel:
Example 1: Find and Replace Strings Using VBA (Case-Insensitive)
Suppose that we would like to replace each occurrence of “Mavs” with “Mavericks” in the range A1:B10.
We can create the following macro to do so:
Sub FindReplace()
Range("A1:B10").Replace What:="Mavs", Replacement:="Mavericks"
End Sub
When we run this macro, we receive the following results:
Notice that each occurrence of “Mavs” has been replaced with the string “Mavericks” in the team column.
Example 2: Find and Replace Strings Using VBA (Case-Sensitive)
Suppose that we would like to replace each occurrence of “Mavs” with “Mavericks” in the range A1:B10 only if the case matches.
We can create the following macro to do so:
Sub FindReplace()
Range("A1:B10").Replace What:="Mavs", Replacement:="Mavericks", MatchCase:=True
End Sub
When we run this macro, we receive the following output:
Notice that this replacement is case-sensitive.
This means that each occurrence of “Mavs” is replaced but each occurrence of “mavs” is left alone.
The following tutorials explain how to perform other common tasks using VBA: