WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Find and Replace

VBA Find and Replace

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Find & Replace Function in VBA

If your excel job involves routine tasks finding something and replacing it 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, and we can implement the same with VBA as well. In our earlier article “VBA Find,” we have shown you how to use the FIND method in VBA. In this article, we will show you how to use the VBA “Find & Replace” method.

Follow the article to learn this technique.

VBA-find-replace

VBA Find and Replace Syntax

To use the Find and Replace method in VBA, we have to follow the below-given steps. First, we have selected the range of cells, so mention the range of cells by 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 whole content or just the part of the content. We can supply two parameters here “xlWhole” &  “xlPart.”
  • Search Order: This is to mention 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 or not. If the case sensitive argument is TRUE or else FALSE.
  • Search Format: We can also search the content by the formatting of the value we are looking for.
  • Replace Format: We can replace the one format with another format as well.

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

Ok, let’s look at the following example to understand VBA Find and Replace Method. 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, Range is from A1 to B15, so the code will be Range (“A1: B15”).

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 our new value we replacing with i.e., “December.”

Code:

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

VBA Find and Replace example 1.6

Ok, 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 the 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 method. For this example, I have created this sample data, as shown in the below image.

vba f&r example 2.1

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

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

Code:

Sub Replace_Example2()

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

End Sub

vba f&r example 2.2

Now 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” to “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 to “hiii.”

So, whenever we want to use MATCH CASE criteria, we should apply the argument as “TRUE,” and 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 has been a guide to 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 Charts
  • What does StrConv do in Excel VBA?
  • End Property in VBA
  • ROUND Function in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Find and Replace Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More