EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • 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
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA Find
 

VBA Find

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated April 8, 2023

VBA Find Function in Excel

 

 

Excel VBA Find Function

Who doesn’t know FIND method in excel? I am sure everybody knows who are dealing with excel worksheets. FIND or popular shortcut key Ctrl + F will find the word or content you are searching for in the entire worksheet as well as in the entire workbook. When you say find means you are finding in cells or ranges isn’t it? Yes, the correct find method is part of the cells or ranges in excel as well as in VBA.

Watch our Demo Courses and Videos

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

Similarly, in VBA Find, we have an option called FIND function which can help us find the value we are searching for. In this article, I will take you through the methodology of FIND in VBA.

Formula to Find Function in Excel VBA

In regular excel worksheet, we simply type shortcut key Ctrl + F to find the contents. But in VBA we need to write a function to find the content we are looking for. Ok, let’s look at the FIND syntax then.

VBA find formula syntax

I know what is going on in your mind, you are lost by looking at this syntax and you are understanding nothing. But nothing to worry before I explain you the syntax let me introduce you to the regular search box.

find window in excel

If you observe what is there in regular Ctrl + F, everything is there in VBA Find syntax as well. Now take a look at what each word in syntax says about.

What: Simply what you are searching for. Here we need to mention the content we are searching for.

After: After which cell you want to search for.

LookIn: Where to look for the thing you are searching For example Formulas, Values, or Comments. Parameters are xlFormulas, xlValues, xlComments.

LookAt: Whether you are searching for the whole content or only the part of the content. Parameters are xlWhole, xlPart.

SearchOrder: Are you looking in rows or Columns. xlByRows or xlByColumns.

SearchDirection: Are you looking at the next cell or previous cell. xlNext, xlPrevious.

MatchCase: The content you are searching for is case sensitive or not. True or False.

MatchByte: This is only for double-byte languages. True or False.

SearchFormat: Are you searching by formatting. If you are searching for format then you need to use Application.FindFormat method.

This is the explanation of the syntax of the VBA FIND method. Apart from the first parameter, everything is optional. In the examples section, we will see how to use this FIND method in VBA coding.

How to Use Excel VBA Find Function?

We will learn how to use a VBA Find Excel function with few examples.

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

VBA Find Function – Example #1

First up let me explain you a simple example of using FIND property and find the content we are looking for. Assume below is the data you have in your excel sheet.

Example 1-1

Step 1: From this, I want to find the name John, let’s open a Visual basic and start the coding.

Code:

Sub Find_Ex1()

End Sub

Example 1-2

Step 2: Here you cannot start the word FIND, because FIND is part of RANGE property. So, firstly we need to mention where we are looking i.e. Range.

VBA Find Example 1-3

Step 3: So first mention the range where we are looking for. In our example, our range is from B2 to B11.

Code:

Sub Find_Ex1()
Range ("B2:B11")
End Sub

VBA Find Example 1-4

Step 4: After mentioning the range put a dot (.) and type FIND. You must see FIND property.

VBA Find Example 1-5

Step 5: Select the FIND property and open the bracket.

VBA Find Example 1-6

Step 6: Our first argument is what we are searching for. In order to highlight the argument we can pass the argument like this What:=, this would be helpful to identify which parameter we are referring to.

Code:

Sub Find_Ex1()
Range ("B2:B11").Find(What:="John")
End Sub

VBA Find Example 1-7

Step 7: The final part is after finding the word what we want to do. We need to select the word, so pass the argument as .Select.

Code:

Sub Find_Ex1()
Range("B2:B11").Find(What:="John").Select
End Sub

VBA Find Example 1-8

Step 8: Then run this code using F5 key or manually as shown in the figure, so it would select the first found word Johnson which contains a word, John.

VBA Find Example 1-9

VBA Find Function – Example #2

Now I will show you how to find the comment word using the find method. I have data and in three cells I have a comment.

Those cells having red flag has comments in it. From this comment, I want to search the word “No Commission”.

VBA Find Example 2-1

Step 1: Start code with mentioning the Range (“D2:D11”) and put a dot (.) and type Find

Code:

Sub Find_Ex2()

Range("D2:D11").Find(
 
End Sub

Example 2-2

Step 2: In the WHAT argument type the word “No Commission”.

Code:

Sub Find_Ex2()

Range("D2:D11").Find(What:="No Commission",

End Sub

VBA Find Example 2-3

Step 3: Ignore the After part and select the LookIn part. In LookIn part we are searching this word in comments so select xlComments and then pass the argument as .Select

Code:

Sub Find_Ex2()

Range("D2:D11").Find(What:="No Commission", LookIn:=xlComments).Select

End Sub

VBA Find Example 2-4

Step 4: Now run this code using F5 key or manually as shown in the figure so it will select the cell which has the comment “No Commission”. In D9 cell we have a mentioned comment.

VBA Find Example 2-5

Deal with Error Values in Excel VBA Find

If the word we are searching for does not find in the range we have supplied VBA code which will return an error like this.

Error

In order to show the user that the value you are searching for is not available, we need the below code.

MSG Box

If the above code found value then it shows the value & cell address or else it will show the message as “The Value you are searching for is not available in the supplied range!!!”.

Things to Remember

  • VBA FIND is part of the RANGE property & you need to use the FIND after selecting the range only.
  • In FIND first parameter is mandatory (What) apart from this everything else is optional.
  • If you to find the value after specific cell then you can mention the cell in the After parameter of the Find syntax.

Recommended Articles

This has been a guide to VBA Find Function. Here we discussed VBA Find and how to use Excel VBA Find Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Function
  2. VBA CDEC
  3. VBA RGB
  4. VBA IsError

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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

Download VBA Find Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW