Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. However, VBA provides a much more efficient way of accomplishing this using the Find method. In this article we’ll have a look at how to use the Range.Find Method in your VBA code. Here is the syntax of the method:

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

where:

What: The data to search for. It can be a string or any of the Microsoft Excel data types (the only required parameter, rest are all optional)

After: The cell after which you want the search to begin. It is a single cell which is excluded from search. Default value is the upper-left corner of the range specified.

LookIn: Look in formulas, values or notes using constants xlFormulas, xlValues, or xlNotes respectively.

LookAt: Look at a whole value of a cell or part of it (xlWhole or xlPart)

SearchOrder: Search can be by rows or columns (xlByRows or xlByColumns)

SearchDirection: Direction of search (xlNext, xlPrevious)

MatchCase: Case sensitive or the default insensitive (True or False)

MatchByte: Used only for double byte languages (True or False)

SearchFormat: Search by format (True or False)

All these parameters correspond to the find dialog box options in Excel.

Return Value: A Range object that represents the first cell where the data was found. (Nothing if match is not found).

Let us look at some examples on how to implement this. In all the examples below, we will be using the table below to perform the search on.

Example 1: A basic search

In this example, we will look for a value in the name column.

    Dim foundRng As Range

    Set foundRng = Range("D3:D19").Find("Andrea")
    MsgBox foundRng.Address

The rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.

The output of this code will be the first occurrence of the search string in the specified range.

If the search item is not found then Find returns an object set to Nothing. And an error will be thrown if you try to perform any operation on this (on foundRng in the above example)

So, it is always advisable to check whether the value is found before performing any further operations.
    Dim foundRng As Range

    Set foundRng = Range("D3:D19").Find("Andrea")

    If foundRng Is Nothing Then
        MsgBox "Value not found"
    Else
        MsgBox foundRng.Address
    End If

Let us know have a look at the optional parameters. To keep it simple, we will exclude the above error handling in the subsequent examples.
Example 2: Using after
    Set foundRng = Range("D3:D19").Find("Andrea", Range("D6"<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>))
    MsgBox foundRng.Address

OR
    Set foundRng = Range("D3:D19").Find("Andrea", After:=Range("D6"))
    MsgBox foundRng.Address

The highlighted cell will be searched for

Example 3: Using LookIn
Before seeing an example, here are few things to note with LookIn

Text is considered as a value as well as a formula
xlNotes is same as xlComments
Once you set the value of LookIn all subsequent searches will use this setting of LookIn (using VBA or Excel)

    'Look in values
    Set foundRng = Range("A3:H19").Find("F4", , xlValues)
    MsgBox foundRng.Address
    'Output --> $B$4

    'Look in values and formula
    Set foundRng = Range("A3:H19").Find("F4", LookIn:=xlFormulas)
    MsgBox foundRng.Address
    'Output --> $B$4

    'Look in values and formula (as previous setting is preserved)
    Set foundRng = Range("H3:H19").Find("F4")
    MsgBox foundRng.Address
    'Output --> $H$4

    'Look in comments
    Set foundRng = Range("A3:H19").Find("F4", , xlNotes)
    MsgBox foundRng.Address
    'Output --> $D$4


Example 4: Using LookAt
    'Match only a part of a cell
    Set foundRng = Range("A3:H19").Find("John", , xlValues, xlPart)
    MsgBox foundRng.Address
    'Output --> $D$8

    'Match entire cell contents
    Set foundRng = Range("A3:H19").Find("John", LookAt:=xlWhole)
    MsgBox foundRng.Address
    'Output --> $D$9

LookAt setting too is preserved for subsequent searches.

Example 5: Using SearchOrder
    'Searches by rows
    Set foundRng = Range("A3:H19").Find("Sa", , , xlPart, xlRows)
    MsgBox foundRng.Address
    'Output --> $G$4

    'Searches by columns
    Set foundRng = Range("A3:H19").Find("Sa", SearchOrder:=xlColumns)
    MsgBox foundRng.Address
    'Output --> $F$5

SearchOrder setting is preserved for subsequent searches.

Example 6: Using SearchDirection
    'Searches from the bottom
    Set foundRng = Range("A3:H19").Find("Alexander", , , , , xlPrevious)
    MsgBox foundRng.Address
    'Output --> $D$18

    'Searches from the top
    Set foundRng = Range("A3:H19").Find("Alexander", SearchDirection:=xlNext)
    MsgBox foundRng.Address
    'Output --> $D$11


Example 7: Using MatchCase
    'Match Case
    Set foundRng = Range("A3:H19").Find("Sa", , , , xlRows, , True)
    MsgBox foundRng.Address
    'Output --> $F$5

    'Ignore case
    Set foundRng = Range("A3:H19").Find("Sa", MatchCase:=False)
    MsgBox foundRng.Address
    'Output --> $G$4


Example 8: Using SearchFormat
When you search for a format it is important to note that the format settings stick until you change them. So, before you use SearchFormat, it is a good practice to always clear any previous formats that have been set.
    'Clear any previous format set
    Application.FindFormat.Clear

    'Set the format for find operation
    Application.FindFormat.Interior.Color = 14348258

    'Match format
    Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=True)
    MsgBox foundRng.Address
    'Output --> $G$15

    'Search without matching the format
    Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=False)
    MsgBox foundRng.Address
    'Output --> $G$4

    'Search only for cells of a particular format
    Set foundRng = Range("B3:H19").Find("*", MatchCase:=True, SearchFormat:=True)
    MsgBox foundRng.Address
    'Output --> $B$3



Example 9: FindNext and FindPrevious
In all the previous examples, we have been looking for just the first occurrence of the search criteria. If you want to find multiple occurrences, we use the FindNext and FindPrevious methods. Both of these need a reference to the last cell found, so that the search continues after that cell. If this argument is dropped, it will keep returning the first occurrence.
    'First occurrence
    Set foundRng = Range("A3:H19").Find("Ms")
    MsgBox foundRng.Address
    'Output --> $C$5

    'Find the next occurrence
    Set foundRng = Range("A3:H19").FindNext(foundRng)
    MsgBox foundRng.Address
    'Output --> $C$10

    'Find the previous occurrence again
    Set foundRng = Range("A3:H19").FindPrevious(foundRng)
    MsgBox foundRng.Address
    'Output --> $C$5


If you are looking to find or list all files and directories in a folder / sub-folder, you can refer to the articles below:
Find and List all Files and Folders in a Directory
Excel VBA, Find and List All Files in a Directory and its Subdirectories
For using other methods in addition to Find, please see:
Check if Cell Contains Values in Excel