In this blog post, we take an in-depth look at the Excel VBA Find function. This function is very useful for (yes, you guessed it), finding things in a list.
The Excel VBA Find function works similar to the Find feature within Excel. Upon finding what you ask for, this function will return the range. We can then perform many actions, or return further information about the found item.
This blog post will demonstrate a few useful examples of the Find function in action.
How Does the Excel VBA Find Function Work?
It runs from a specified range and the only information the Find function needs is the value you want to search for. It does have quite a few optional arguments that can also be used.
You can see a list of these below with a brief explanantion of their use. This blog post will demonstrate some of the more useful options.
- What – The only required option. The value you want to search for.
- After – A starting cell range for the search
- LookIn – Where to search for the value – comments, formulas or values.
- LookAt – Whether you want to search for a complete match on the value, or a partial match.
- SearchOrder – You can order to search the range by rows, or by columns.
- SearchDirection – You can search the range forwards (xlNext), or backwards(xlPrevious).
- MatchCase – A True or False value as to whether you want a case sensitive search or not.
- MatchByte – A True or False value or double byte languages.
- SearchFormat – A true or False value to search by format, or not.
Performing a Simple Find
Let’s see a simple example of the FIND function in action.
We have the list of names below.
The following VBA can be used to find the name Gita and return the address of the found cell. The address (A5) is printed to the Immediate window so that we can see the result.
Dim FoundItem As Range
Set FoundItem = Range("A2:A7").Find("Gita")
Debug.Print FoundItem.Address
Beginner to Excel VBA? Enrol in our online Excel VBA course and get up to speed fast.
You can create a similar list to play around with this code and learn what else you can do with a found item.
The address is returned in this example to see it working correctly. Depending on your real-world requirements, you can return any information about that cell. Or perform actions to it.
Take the following example, where we want to write the current date to the cell in the column next to the found item.
Dim FoundItem As Range
Set FoundItem = Range("A2:A7").Find("Gita")
FoundItem.Offset(0, 1) = Date
When the Find function is used in Excel VBA, the criteria of the last search will be visible when using the Find feature in Excel.
This also applies to some of the options explained soon and is the same as how the Find dialog in Excel normally operates.
Preparing for When a Value is Not Found
There may be times when the Find function cannot find the value it is looking for. We need to write some code to handle these scenarios. Otherwise our macro will return an error message.
When the Excel VBA Find function cannot find the value, it returns the range object set to nothing.
So to handle this we can use an IF statement to test if the returned object is equal to nothing. And if so take a different course of action.
In the code below a message is shown to the user, and then the macro is aborted.
Dim FoundItem As Range
Set FoundItem = Range("A2:A7").Find("Paul")
If FoundItem Is Nothing Then
MsgBox "The name was not found."
Exit Sub
Else
FoundItem.Offset(0, 1) = Date
End If
Using the LookIn Option with Find in Excel VBA
The Find function can do more than just look for the values in a cell. It can also look for text within formulas and comments.
In the image below you can see the word “Completed” has been entered into a comment for cell A4.
The following code searches for text within a comment, and prints the address of that cell to the Immediate window.
Dim FoundItem As Range
Set FoundItem = Range("A2:A7").Find("Completed", LookIn:=xlComments
Debug.Print FoundItem.Address
Performing a Partial Match When Searching for a Value
Another really useful option when working with the Find function is the ability to look for a partial match.
The Find function has an argument named LookAt which we can use to specify to look at the whole value, or just part of it.
In the list of buildings below, we could use this option to search for “Chicago”, even though it is only a part of the value of a cell.
The code below searches for “Chicago” as a partial match and prints the address to the Immediate window.
Dim FoundItem As Range
Set FoundItem = Range("A2:A7").Find("Chicago", LookIn:=xlValues, LookAt:=xlPart)
Debug.Print FoundItem.Address
Remember that previous Find criteria can remain after a user has run Find from a macro, or the dialog in Excel.
Therefore it can be a good idea to specify arguments such as LookIn:=xlValues to overwrite any previous setting such as comments, which we used in the previous example of this blog post.
Find a Cell with Specific Formatting
The Find function can also be used to find a cell based on its formatting.
So for example in the list of cities below, I want to find the “Rome” in a bold format.
To use this option, you first need to set the format separately. And then use the SearchFormat argument of the Find function.
The code below shows the bold font formatting being set, and then in a separate statement a search for that formatting is set to true.
Dim FoundItem As Range
Application.FindFormat.Font.Bold = True
Set FoundItem = Range("A2:A7").Find("Rome", SearchFormat:=True
Debug.Print FoundItem.Address
Any formatting that you set will still be there when you run the Find function again. Therefore it is a good idea to clear the FindFormat property after its use with the following statement.
Application.FindFormat.Clear
The Find function can be very useful and the various options provided with it means that we have great ability to find exactly what we want.
This blog post explored some of the more useful options. Please explore what else it can do and see how it can help you in your future macros.
Leave a Reply