excelavon

Excel VBA Find Method: Syntax, Examples, and Tips

VBA & macros · Updated November 3, 2026

Excel VBA Find Method: Syntax, Examples, and Tips

In Excel VBA, use the Range.Find method to search a specified range and return the first matching cell; check whether the result is Nothing before using it. For predictable results, explicitly set options such as LookIn, LookAt, and SearchOrder, because Excel can retain some Find settings from earlier searches.

What the VBA Find Method Does

Find searches cells in a Range object and returns a Range object for the first match it encounters. It does not select the result or change the worksheet. This makes it useful in macros that need to locate a label, check whether a value exists, jump to a record, or process every matching cell.

The search range can be a small block, a column, a worksheet’s used range, or all cells on a worksheet. A bounded range is usually preferable: searching all cells can take longer than necessary and may find an unexpected match far from the data. Qualify the range with a worksheet variable rather than relying on whichever sheet happens to be active.

If there is no match, the method returns Nothing. That is a normal result, not an error, so test for it before reading properties such as Address or Value. The method locates cells; it does not by itself decide what action to take after a match.

Syntax and the Arguments That Matter

The general form is someRange.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat). The arguments after What are optional, but leaving them out can make a macro depend on previous Find settings. In routine code, supply the options that define the intended search.

  • What is the text, number, or other value to find.
  • After is one cell within the search range. Excel begins after that cell, then wraps around to the start. To begin with the first cell, set After to the range’s last cell.
  • LookIn selects what Excel searches, commonly xlValues for cell values or xlFormulas for formula content.
  • LookAt is xlWhole for a whole-cell match or xlPart for a match within the cell.
  • SearchOrder is xlByRows or xlByColumns; it controls the order in which cells are examined.
  • SearchDirection is xlNext or xlPrevious.
  • MatchCase determines whether letter case matters. MatchByte is relevant to double-byte language support.
  • SearchFormat controls whether the search is limited by formatting criteria.

For example, xlWhole searching for “East” will not match “Northeast,” while xlPart can. Choosing the right combination matters more than the order of the named arguments in a VBA statement.

Find One Match: A Complete Example

This macro searches A2:D100 on a worksheet named Data for a cell whose value is exactly “North,” ignoring case. Because After is set to the last cell in the range, the first cell examined is A2. The code reports the address if it finds a match and gives a separate message if it does not.

Sub FindNorthOnce()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range

    ' Search only the intended data block.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:D100")

    Set foundCell = searchRange.Find( _
        What:="North", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No exact match was found."
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
End Sub

Before running a macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, even when a later version of the macro changes or clears data. This example only displays a message, but the precaution is important when adapting search code to edit cells.

Finding Every Occurrence with FindNext

Find returns just one result. To process every match, record the first address, then call FindNext repeatedly until the search wraps back to that address. The address check prevents an endless loop. The following example counts exact “North” values in the same range:

Sub CountNorthMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim matchCount As Long

    ' Search the same range with explicit options.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:D100")
    Set foundCell = searchRange.Find( _
        What:="North", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No matches were found."
        Exit Sub
    End If

    firstAddress = foundCell.Address
    Do
        matchCount = matchCount + 1
        Set foundCell = searchRange.FindNext(After:=foundCell)
        If foundCell Is Nothing Then Exit Do
        If foundCell.Address = firstAddress Then Exit Do
    Loop

    MsgBox matchCount & " match(es) found."
End Sub

Use the same search range and settings for FindNext as for the initial search. If your goal is to edit every matching cell, perform the action inside the loop, but take care with changes that alter the search value: a cell may stop matching before the next search call.

Values, Formulas, Partial Matches, and Wildcards

Use LookIn:=xlValues when you want to find the cell’s value, including the calculated result of a formula. Use LookIn:=xlFormulas when searching formula content itself. For instance, if a cell displays 25 because it contains a formula, a value search targets 25; a formula search can target text in the formula expression. Choose based on whether the question concerns what the cell contains as a result or how that result is calculated.

For ordinary text searches, xlWhole means the whole cell must match and xlPart allows a substring. Excel Find also supports wildcard criteria: an asterisk represents any sequence of characters, and a question mark represents one character. To search for a literal asterisk or question mark, prefix it with a tilde. Wildcards are useful for broad searches, but they can also make a criterion less specific than intended.

Case sensitivity is a separate setting. Setting MatchCase:=True distinguishes “North” from “north”; setting it to False does not. Do not assume that an exact whole-cell search is also case-sensitive: LookAt and MatchCase govern different aspects of the match.

Search Order, Direction, and the After Cell

The search order determines how Excel moves through a multi-cell range. xlByRows scans across a row and then continues on the next row; xlByColumns scans down a column before moving to the next one. This can affect which duplicate is returned first, so set the order to fit how the records are arranged.

SearchDirection:=xlNext searches forward through that order, while xlPrevious searches in reverse. The After cell is a starting reference, not a cell that is tested first: the search begins after it and wraps around. It must be a single cell in the range being searched. Setting it to the range’s final cell is a practical way to make a forward search start at the first cell.

Common Problems and Reliable Fixes

  • The result is Nothing: confirm the worksheet, range, spelling, and LookIn choice. A displayed result and formula text are not interchangeable search targets.
  • A partial or unexpected match appears: specify LookAt:=xlWhole when the entire cell must match, and check whether wildcards are present.
  • The macro returns a different duplicate: set both SearchOrder and SearchDirection, and choose an intentional After cell.
  • Results change after using Find in the interface: Excel can preserve Find options between searches. Pass all relevant arguments in VBA instead of relying on the dialog’s last settings.
  • A format search behaves unexpectedly: use SearchFormat:=False for a content-only search. When searching by formatting, configure the Find format criteria deliberately; format criteria can remain in effect from a prior operation.
  • The macro fails on another sheet: fully qualify the worksheet and range. Unqualified Range references can point to the active sheet, which may not be the intended one.

Also avoid using foundCell.Address until after checking that foundCell Is Nothing. A missing result is a routine possibility, and treating it as a valid Range causes a runtime error.

Find Compared with Other Search Options

Range.Find is a good fit when VBA needs a cell reference and may need to visit several matches. The worksheet MATCH function is more suitable when a formula should return a position in a row or column. VBA’s InStr function checks whether one string occurs inside another string, but it does not search a worksheet range by itself. These tools solve related but different problems.

For a single known column, a direct loop through its cells can be easier to customize, especially when the test involves several conditions or complex comparisons. However, a loop may be slower on a large range if it reads cell values one at a time. For straightforward locating and iteration, Find plus FindNext is usually clearer.

Excel for Mac and Excel for the Web

The VBA Range.Find approach applies to desktop Excel for Microsoft 365 on Windows and is also available in desktop Excel for Mac, though the VBA editor and some keyboard shortcuts differ by platform. Excel for the web does not run VBA macros. A workbook containing VBA can be opened there, but use desktop Excel to write or execute the macro. If a workbook must be automated in a browser, consider Office Scripts instead; it is a different automation model, not VBA.

Frequently Asked Questions

How do I find a value in a column with VBA?

Set a Range variable to the column’s data cells, then call Find with the value and explicit options. Check whether the returned Range is Nothing before using it. Limiting the search to the populated rows avoids searching irrelevant cells.

What does Find return when there is no match?

It returns Nothing. Use an If foundCell Is Nothing Then check before accessing the result’s address, value, or other properties.

How do I find part of a cell’s text?

Set LookAt:=xlPart. To locate text patterns, you can also use wildcard characters such as * and ?, remembering that a tilde escapes a wildcard when you need to search for it literally.

Why does VBA Find return a different result each time?

It may be using retained Find settings, or it may encounter duplicates in a different search order or direction. Explicitly set the relevant arguments, including LookIn, LookAt, SearchOrder, and SearchDirection.

Can Find search formulas instead of displayed values?

Yes. Set LookIn:=xlFormulas to search formula content, or use LookIn:=xlValues to search values such as a formula’s calculated result. The correct choice depends on what you are trying to match.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.