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 ExcelFind And Replace In ExcelFind and Replace is an Excel feature that allows you to search for any text, numerical symbol, or special character not just in the current sheet but in the entire workbook. Ctrl+F is the shortcut for find, and Ctrl+H is the shortcut for find and replace.read more 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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 VBAUsing RANGE Object In VBARange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.

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

Select the Replace method from the list.

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

Examples of VBA Find and Replace in Excel

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

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.

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

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

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

Step 4: Mention what parameter as “September.”

Range(“A1:B15”).Replace What:=“September”

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

Range(“A1:D4”).Replace What:=“September”, Replacement:=“December”

As of now, ignore all the other parameters. Now, run the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to see the replacement method with VBA.

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.

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.

Sub Replace_Example2()

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

End Sub

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

Range(“A1:D4”).Replace What:=“HELLO”, Replacement:=“Hiii”, MatchCase:=True

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

Imagine you have not applied the Match Case argument in VBAMatch Case Argument In VBAIn VBA, the match function is used as a lookup function and is accessed by the application. worksheet method. The arguments for the Match function are similar to the worksheet function.read more, then it will replace all the “Hello” with “Hiii.”

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

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: –

  • VBA ChartsWhat does StrConv do in Excel VBA?End Property in VBAROUND Function in VBA