EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Find

By Jeevan A YJeevan A Y

Home » VBA » Blog » VBA String and Text Functions » VBA Find

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.

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.

Watch our Demo Courses and Videos

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

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

All in One Software Development Bundle (600+ Courses, 50+ projects)

600+ Online Courses

50+ projects

3000+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Watch our Demo Courses and Videos

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

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

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

EDUCBA
Watch our Demo Courses and Videos

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

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

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

EDUCBA Login

Forgot Password?

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

Let’s Get Started

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

EDUCBA

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

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

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More