EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA Find and Replace

VBA Find and Replace

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Find and Replace

Excel VBA Find and Replace

Have you ever thought of automating Find and Replace function in Excel. In VBA, we can create a macro which can be used for finding and replacing anything which we normally do in Excel by pressing Ctrl + H shortcut keys. With the help of VBA Find and Replace, we can automate the finding of any word and replacing that with other replacement. This helps when we need to perform the same activity multiple times.

How to Find and Replace Words in Excel VBA?

Below are the different examples to find and replace the words in excel using VBA code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

VBA Find and Replace – Example #1

In a very simple example, we have a data set which consists of some name. And some of the names are getting repeated as shown below.

VBA Find and Replace Example 1-1

Now we will create a code, by which we will find any of the names which is getting repeated and replace with something else. For this, follow the below steps.

Step 1: Go to Insert menu in VBA and select Module option as shown below.

VBA Find and Replace Example 1-2

Step 2: Once we do that, we will get a new module opened. In that module, write the subcategory in the name of VBA Find and Replace or you can choose any name of that subcategory.

Code:

Sub Find_Replace1()

End Sub

VBA Find and Replace Example 1-3

Step 3: As we have the data in column B from cell B2 to B10, so we will select that Range first from B2:B10.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace

End Sub

Select Range Example 1-4

Step 4: Now to replace something we need to look for that word. Here also we will first select the Replace function to look at what we need to replace.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace

End Sub

VBA Find and Replace Example 1-5

As per the syntax of the Replace function, we will find what we need to replace.

Step 5: Let’s select the first name of the list “Ben” as what we need to replace.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace What:="Ben"

End Sub

VBA Find and Replace Example 1-6

Step 6: Now choose the replacement word by which we need to replace the selected word. Here we chose to replace name Ben with Sam which is mentioned as Replacement as shown below.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace What:="Ben", Replacement:="Sam"

End Sub

VBA Find and Replace Example 1-7

Step 7: Now compile the code and run it by clicking on the Play button or by pressing the F5 key, as shown below.

Result of Example 1-8

We will see, all the cells containing the name as Ben is now replaced with name Sam. And that is highlighted in yellow color as well.

VBA Find and Replace Example 1-9

VBA Find and Replace – Example #2

We all might have faced a situation where we tried to find and replace some words with case sensitive letters. Suppose in a list, if we want to replace only that word which has some lower case or upper case letters. And if we are using the process which we have seen in example-1 then we would end up replacing all the similar words in that list along with required one. This process cannot be done with the help of find and replace function (Ctrl + H) of excel. For this, we have a provision in VBA.

Let’s consider the below data for this. As we can see, we have purposely added a word BEN in upper case in cell B2.

VBA Find and Replace Example 2-1

Follow the below steps to find and replace the word BEN.

Step 1: Start the subcategory of VBA Find and Replace there as shown below.

Code:

Sub Find_Replace2()

End Sub

VBA Find and Replace Example 2-2

Step 2: Select the list as Range from B2 to B10.

Code:

Sub Find_Replace2()

Range("B2:B10")

End Sub

Select Range Example 2-3

Step 3: Now choose the exact word which we want to replace. Here we have selected BEN which is in cell B2.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN"

End Sub

VBA Find and Replace Example 2-4

Step 4: Again, we have selected the same word which we have seen in example-1 as replacement of BEN as Sam.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam"

End Sub

Replacement Example 2-5

Step 5: Now for replacing the exact word BEN, we need to use MatchCase if that becomes TRUE as shown below.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam", MatchCase:=True

End Sub

MatchCase Example 2-6

The MatchCase will help us in identifying that word which we want to replace with an exact match. Here, the word which we have selected is BEN in upper case and list has other similar words as well but in proper case.

Step 6: Now run the code by clicking on the Play button or by pressing the F5 key.

Result of Example 2-7

We will see, the word BEN which was at cell B2 is now replaced with the word “Sam”, highlighted in yellow color. And the similar words Ben which are located at cells B5 and B8 respectively are still unchanged.

VBA Find and Replace Example 2-8

This is how exact match replaces works.

Step 7: Now, we will remove the added MatchCase from the above code and see how this would work.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam", MatchCase:=True

End Sub

VBA Find and Replace Example 2-9

Step 8: Again compile and run the code.

VBA Find and Replace Example 2-10

We will see the code has replaced all the cells containing word Ben as shown above. Which means, after removing MatchCase, the code will work as we have seen example 1.

Pros of Excel VBA Find and Replace

  • Major learning is, we can replace case sensitive words and cell content with the help VBA Find and Replace as shown in example-2.
  • If the activity is manually and multiple times getting repeated then automating this would be an advantage in saving time and effort both.
  • Even if we have a huge set of data where we want to replace specific words, that can be done without crashing the excel in bulky files.

Things to Remember

  • We can create a macro of Find and Replace function with the help of Record Macro option under the Developer tab. This is the easiest way to create a macro if you are new to coding.
  • Saving the file in Macro Enable excel format will enable to code getting used in future.
  • We can replace any kind of text, word, number or character by VBA Find and Replace.

Recommended Articles

This is a guide to VBA Find and Replace. Here we discuss how to find and replace words in Excel using VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Break for Loop
  2. VBA Get Cell Value
  3. VBA Find
  4. FIND Function in Excel
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

EDUCBA

Download VBA Find and Replace Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW