• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA Find and Replace

Home » Excel » Blog » VBA » VBA Find and Replace

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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.

Popular Course in this category
Cyber Week Sale
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,676 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

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. REPLACE Formula in Excel
  3. A Complete Guide VBA Find
  4. FIND Function in Excel

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download VBA Find and Replace Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

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 Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More