FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Find and Replace

Updated on June 7, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Find & Replace Function in VBA

If your Excel job involves routine tasks finding and replacing something with something, then you need this article at any cost. Because after reading this article, you would probably save 80% of your time by learning this VBA coding technique. Find and Replace in Excel is an often used tool. We can implement the same with VBA as well. Our earlier article, “VBA Find,” shows you how to use the FIND method in VBA. This article will show you how to use the VBA “Find & Replace” method.

Follow the article to learn this technique.

VBA Find and Replace

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Find and Replace (wallstreetmojo.com)

VBA Find and Replace Syntax

We must follow the steps below to use the Find and Replace method in VBA. First, we have selected the range of cells, so mention the range of cells using RANGE object in VBA.

find&replace VBA explanation

Now put a dot (.) to see the IntelliSense list.

find&replace VBA explanation1

Select the Replace method from the list.

find&replace VBA explanation2

We can see the huge parameter list of the Replace method. Now, we will see each parameter explanation below.

  • What: This is nothing but what we need to find to replace the value.
  • Replacement: With the found value, what should be the new value to be replaced with?
  • Look At: This is to mention whether we want to look at the full content or just the part of the content. We can supply two parameters here “xlWhole” & “xlPart.”
  • Search Order: This mentions the search order, either rows or columns. We can supply two parameters here “xlByRows” & “xlByColumns.”
  • Match Case: The content we are searching for is case-sensitive. Suppose the case-sensitive argument is TRUE or else FALSE.
  • Search Format: We can also search the content by formatting the value we are looking for.
  • Replace Format: We can replace one format with another format as well.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of VBA Find and Replace in Excel

Below are some examples of the Excel VBA Find and Replace method.

You can download this VBA Find and Replace Excel Template here – VBA Find and Replace Excel Template

Example #1 – VBA Find and Replace the Word

Let us look at the following example to understand the VBA Find and Replace method. First, take a look at the following data.

VBA Find and Replace example 1.1

Step 1: First, mention the Range of cells we are replacing. In this example, the range is from A1 to B15 so the code will be Range (“A1: B15”).

Code:

Sub Replace_Example1()

Range ("A1:B15")

End Sub
VBA Find and Replace example 1.2

Step 2: Now, put a dot to see the IntelliSense list.

VBA Find and Replace example 1.3

Step 3: Select the Replace method from the IntelliSense list.

VBA Find and Replace example 1.4

Step 4: Mention what parameter as “September.”

Code:

Range("A1:B15").Replace What:="September"
VBA Find and Replace example 1.5

Step 5: Next, Replace with parameter should be the new value we are replacing with, i.e., “December.”

Code:

Range("A1:D4").Replace What:="September", Replacement:="December"
VBA Find and Replace example 1.6

As of now, ignore all the other parameters. Now, run the VBA code to see the replacement method with VBA.

vba f&r example 1.7

So, it has replaced all of September with the word “December.”

Example #2 –  Case Sensitive Replacement

The more advanced example of the VBA Find & Replace method will be using case sensitive replacement methods. We have created this sample data for this example, as shown in the image below.

vba f&r example 2.1

We have two cell data in capital letters, “HELLO.” Wherever we have a capital “HELLO,” it should be replaced by the new word “Hiii.”

As usual, write the code, and mention what to find and replace first.

Code:

Sub Replace_Example2()

Range("A1:D4").Replace What:="HELLO", Replacement:="Hiii"

End Sub
vba f&r example 2.2

For the next argument, “Match Case,” write the condition as TRUE.

Code:

Range("A1:D4").Replace What:="HELLO", Replacement:="Hiii", MatchCase:=True
vba f&r example 2.3

Now, run the code. It will replace only the capital “HELLO” with “Hiii.”

vba f&r example 2.4

Imagine you have not applied the Match Case argument in VBA, then it will replace all the “Hello” with “Hiii.”

vba f&r example 2.2

Note: I have removed the Match Case argument here. By default, the MATCH CASE argument value is FALSE.

vba f&r example 2.5

As we can see in the above image, it has replaced all the “hello” words with “hiii.”

So, whenever we want to use MATCH CASE criteria, we should apply the argument as “TRUE.” By default, this argument value is “FALSE.” Like this, we can use the “FIND & REPLACE” method to find something and replace the found value with something else.

Recommended Articles

This article has been a guide to the VBA Find and Replace Method. Here we will learn how to find and replace the word in VBA Excel with examples and downloadable Excel sheets. You may also have a look at other articles related to Excel VBA: –