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.
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.
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.
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.
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.
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
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.
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
Step 4: After mentioning the range put a dot (.) and type FIND. You must see FIND property.
Step 5: Select the FIND property and open the bracket.
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
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
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 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”.
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
Step 2: In the WHAT argument type the word “No Commission”.
Code:
Sub Find_Ex2() Range("D2:D11").Find(What:="No Commission", End Sub
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
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.
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.
In order to show the user that the value you are searching for is not available, we need the below code.
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 –
