VBA Find in Range: Search Cells and Return Matches

To find a value in an Excel range with VBA, use the range’s Find method and check whether it returned a cell: Set found = searchRange.Find(What:="Blue", LookIn:=xlValues, LookAt:=xlWhole). If no cell matches, found is Nothing; otherwise, it refers to the matching cell.

What VBA Find in a Range Does

Range.Find searches cells and returns a Range object for the first match it encounters. That lets your macro do more than identify whether a value exists: it can read the cell’s address, inspect its value, or change the cell. The method is useful for searches in a column, a table, or a larger worksheet area when the position of the matching cell may change.

A search is limited to the range you call Find on. For example, searching Sheet1.Range("A2:A100") does not search the rest of the worksheet. Choose the range deliberately so the macro does not accidentally find a heading, a note, or a match in an unrelated section.

The method returns only the first match in its search order. If there may be multiple matching cells, use FindNext to continue through them. In either case, test the result before referring to properties such as Address or Value.

Find the First Matching Cell

This example looks for the whole value “Blue” in cells A2:A100 on the active sheet. It prints the address and value of the first match in the VBA editor’s Immediate window. The search options are written out so the result does not depend on many settings left over from an earlier Excel Find operation.

Sub FindBlueInRange()
    Dim searchRange As Range
    Dim found As Range

    ' Search only the intended cells on the active worksheet.
    Set searchRange = ActiveSheet.Range("A2:A100")

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

    If found Is Nothing Then
        Debug.Print "No match found."
    Else
        Debug.Print "Found at " & found.Address & ": " & found.Value
    End If
End Sub

In this example, After specifies the last cell in the search range. Excel begins after that cell and wraps around, so the first cell in the range is considered. SearchOrder:=xlByRows means the search proceeds across rows before moving down; in a single-column range, it simply moves down the column.

To use the code, open the Visual Basic Editor, insert a standard module, and run the procedure. In the editor, open the Immediate window from View > Immediate Window to see the printed result. The macro searches the active sheet, so activate the intended worksheet first or replace ActiveSheet with a specific worksheet reference.

Choose the Search Options Deliberately

The arguments to Find control what counts as a match and where Excel looks. Common choices include:

What is the text, number, date, or other value to locate.
LookIn:=xlValues searches cell values, including the calculated results shown for formulas. Use LookIn:=xlFormulas when you want to search the formula text itself.
LookAt:=xlWhole requires the whole cell value to match. LookAt:=xlPart finds the search text anywhere within a cell.
SearchOrder:=xlByRows searches row by row. xlByColumns searches column by column.
SearchDirection:=xlNext moves forward through the search order. xlPrevious searches in the other direction.
MatchCase:=True makes text matching case-sensitive; False ignores case.
SearchFormat:=False prevents a saved cell-format search from affecting the result.

For example, with LookAt:=xlPart, searching for “art” can match “carton” or “article”; with xlWhole, it matches only a cell whose complete content is “art”. A full match is usually the safer choice for IDs, codes, and names when partial matches could identify the wrong record.

Find Every Match, Not Just the First

When a value can occur repeatedly, record the address of the first match and keep searching until Excel returns to it. The address is a useful stopping point because the search wraps around to the beginning of the range. This macro prints every whole-cell match for “Blue” in A2:A100:

Sub FindAllBlueMatches()
    Dim searchRange As Range
    Dim found As Range
    Dim firstAddress As String

    ' Search only the intended cells on the active worksheet.
    Set searchRange = ActiveSheet.Range("A2:A100")

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

    If found Is Nothing Then Exit Sub

    firstAddress = found.Address
    Do
        Debug.Print found.Address & ": " & found.Value
        Set found = searchRange.FindNext(After:=found)
        If found Is Nothing Then Exit Do
    Loop While found.Address <> firstAddress
End Sub

FindNext continues the search using the criteria from the preceding Find call. The explicit Nothing check prevents an error if there are no results or the search cannot continue. The address check prevents an endless loop after the search wraps back to its starting match. To collect results somewhere instead of printing them, replace the Debug.Print line with code that writes each address or value to a designated output range.

Search Formula Results, Formula Text, and Wildcards

Decide whether you want to find what a cell displays or the formula it contains. Suppose B2 contains =A2*2 and currently displays 40. With LookIn:=xlValues, a search for 40 can match the calculated result. With LookIn:=xlFormulas, a search can target text in the formula, such as part of the expression. This distinction matters when a worksheet’s results change but its formulas remain the same.

For partial text searches, Excel Find recognizes wildcard characters. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) escapes a wildcard when you need to search for the literal character. For example, a search term of AB* with LookAt:=xlWhole can match a cell beginning with AB followed by any text. Use wildcards only when that flexibility is intended; for a literal asterisk or question mark, escape it with a tilde.

Numbers and dates can also lead to surprising matches because Excel stores dates as numbers and applies number formats to display them. If a search is meant to find a date or numeric identifier, ensure the cells contain the expected values and choose LookIn and LookAt to suit the data. Formatting a number to look like text does not necessarily change its underlying value.

Common Find Problems and Their Fixes

A frequent source of inconsistent results is that Excel remembers several Find settings between searches. The Find dialog and VBA searches can share settings such as whether to match whole cells, search formulas or values, and use a format. If a macro omits optional arguments, its behavior can depend on the remembered state. Supplying the important arguments explicitly, as in the examples, makes the search more predictable.

Other common problems have straightforward checks:

The result is Nothing: Confirm the search range, spelling, data type, and LookIn setting. Check for leading or trailing spaces in text.
A partial or unintended match appears: Use LookAt:=xlWhole instead of xlPart, and decide whether case sensitivity is needed.
The wrong worksheet is searched: Replace ActiveSheet with a worksheet reference tied to the correct workbook and sheet.
The macro errors after finding nothing: Test If found Is Nothing Then before reading the cell’s address or value.
A loop repeats forever: Save the first match’s address and stop when the search returns to it.

Hidden rows do not automatically make a cell outside the specified range, and a search should not be treated as a filter for visible records only. If the task is to process visible cells, build that requirement into the macro separately rather than assuming Find skips hidden data.

When to Use Find Instead of Other Methods

Find is a strong choice when VBA needs the matching cell itself, when the search range is not a simple fixed lookup column, or when you need options such as partial matching and searching formula text. Once it returns a cell, you can use that cell’s row or column to retrieve related information. For example, if a product ID is found in column A, the matching row number can guide a macro to read the product description from column B.

For a simple one-dimensional lookup where you only need a position, VBA’s Application.Match can be more compact. For a worksheet formula that returns a related value, lookup functions such as XLOOKUP may be easier to maintain than a macro. These alternatives have different return values and behavior: Find returns a cell reference, whereas a lookup formula returns a value or result. Choose based on whether the task needs a cell to act on, a position, or a value to display.

For repeated searches in a very large dataset, consider whether the search can be limited to a smaller range or whether loading data into an array and checking it in memory better suits the task. That approach takes more code and care, so it is not automatically preferable. For ordinary worksheet searches, a clearly scoped Find call is often easier to read and maintain.

Run VBA Safely on Windows and Mac

VBA macros can change workbook contents, and Excel does not provide a Ctrl+Z undo for actions performed by a macro. Save a separate copy of the workbook before running a macro that edits or deletes cells. The examples above only print results, but adapting them to modify cells changes the risk.

On Windows, the Visual Basic Editor can usually be opened with Alt+F11. On Mac, the equivalent shortcut is commonly Option+F11, though keyboard settings can affect function-key behavior; the editor is also available from Excel’s Developer tools when enabled. The VBA Range.Find method works in desktop Excel for Windows and Mac. Excel for the web does not run VBA macros; use desktop Excel to run this code.

Frequently Asked Questions

How do I check whether VBA Find found anything?
Test the returned range with If found Is Nothing Then. Only access properties such as found.Address after confirming that a match was returned.

How do I find a partial match in VBA?
Set LookAt:=xlPart in the Find call. This searches for the term within a cell’s content rather than requiring the entire cell to match.

How do I make VBA Find case-sensitive?
Set MatchCase:=True. With MatchCase:=False, uppercase and lowercase letters are treated as equivalent for the text search.

Why does VBA Find return the wrong result?
Check that the intended range is being searched and explicitly set LookIn, LookAt, search order, direction, case sensitivity, and format behavior. Excel can retain Find settings from earlier searches.

How can I find all occurrences in a range?
Use Find for the first match, then repeatedly call FindNext. Save the first match’s address and stop when the search returns to it, checking for Nothing along the way.

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.