How To Use Excel VBA Find, With Example!

Finding a value in a sheet can be very handy when using Excel VBA, within macros or sheet code. Luckily, VBA has a Find function that you can call on a Range, which can find values in the sheet. It also allows you to configure how the function will search for whatever you’re looking for. Let’s take a look at how Excel VBA Find works!

Say you had some values in a sheet and you wanted to write a macro that did something when it found a specific value. In the above sheet, I have a grid of numbers of different values. I am going to write a macro that looks for the number 9 and, if it is found, writes the word ‘found’ in a different cell:

Sub find()
    Set c = Range("A1:E9").find(9)
    If Not c Is Nothing Then:
        ActiveSheet.Cells(10, 10).Value = "found"
End Sub

And when I ran this:

Pretty cool! A few things to note here:

1. You have to call find on a range, by writing Range.Find(). A range can be passed in or triggered off another event.
2. In this case, we only specified the value we wanted to find, but the Find function has many optional properties.
3. Find returns a range, so to check if it worked or not, you check if the return value is not Nothing. If so, you can then do whatever you wanted if the Find was successful

What else can Find do?

The official Excel help docs show the syntax of Find to be the following:

expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

After allows you to specify the cell in the range you want the search to start after. This is helpful if you are looking to find and preform actions on items in a certain order or only within a certain subrange.

LookIn and LookAt take in Excel enumerated values. They allow you to specify whether to look in cells with comments, formulas, values, and whether to match against the whole cell value or a partial cell value.

SearchOrder and SearchDirection also take in enumerated values, and they allow you to specify the direction of the search and whether to search by rows or columns.

MatchCase, MatchByte and SearchFormat allow you to fine tune the search so cases and byte values match.

But why would any of this matter? Find is often used to programmatically go through a sheet and find values so things can be edited or highlighted. Let’s take a look at the same sheet and use the Find function again, with a tweak:

Sub find()
    Set c = Range("A1:E9").find(9)
    If Not c Is Nothing Then:
        Debug.Print c
        c.Value = "found"
End Sub

Run again two more times and:

Now, if we wanted to only have the cell be replaced with ‘found’ in a certain order, the other params would make more sense! Take this sheet:

Say I wanted find to start after cell D2, so A3 was found first:

Sub find()
    Set c = Range("A1:E9").find(9, [D2])
    If Not c Is Nothing Then:
        Debug.Print c
        c.Value = "found"
End Sub

Pretty cool right? The other parameters would work much in the same way, and you can fine-tune your Find function for whatever purpose you need. Now that you now how to use the Excel VBA Find method, see if you can learn more about Excel VBA!