How to Use Find and Replace in VBA (With Examples)

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.

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: