Excel VBA Find Cell: Code, Options, and Examples

In Excel VBA, use the Range.Find method to locate a cell containing specified text or a value. Set a worksheet and search range, call Find with the match options you need, then check whether the result is Nothing before using the cell.

Find one cell with VBA

This example searches A1:D100 on the worksheet named Data for an exact match to the text “North”. It reports the address if it finds a cell and gives a message if it does not. Put the code in a standard VBA module; change the sheet name, range, and search text to fit your workbook.

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

    ' Set the worksheet and the cells to search.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A1:D100")

    ' Search for a whole-cell match in calculated values.
    Set foundCell = searchRange.Find( _
        What:="North", _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

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

In this example, the result address is relative to the worksheet, so a match in cell B7 displays as B7. Using ThisWorkbook points to the workbook containing the macro. If the code is stored in a different workbook from the data, identify the data workbook explicitly instead of assuming the active workbook is the right one.

What Range.Find does

Find searches a specified Range object and returns the first matching cell as another Range object. It does not change the cell. You can read properties such as Value, Formula, or Address from the result, or use the returned range as the target for another operation. If there is no match, the return value is Nothing, which is why the conditional check matters.

The search range controls the boundaries. ws.Range("A1:D100") searches only those 400 cells; ws.Cells searches the worksheet’s cells. The worksheet should be specified directly, as in ws.Range, rather than relying on ActiveSheet. Active-sheet references can silently search the wrong tab when a user changes the selection or activates another sheet.

Excel’s Find dialog and VBA’s Find method share search settings. Some settings can carry over from the dialog or a previous search, so a macro should explicitly provide the options that matter instead of relying on whatever Excel last used. The example specifies the important match and direction options and turns off format-based searching.

Choose the right search options

The named arguments determine what Excel considers a match and the order in which it looks. These are the options most likely to affect the result:

What is the value or text to look for. It can be a string, number, or other supported search value.
LookIn:=xlValues searches cell values, including calculated results. LookIn:=xlFormulas searches formula content, which is useful when looking for a formula that contains particular text. For notes or threaded comments, Excel has separate search constants, but support can depend on the Excel object model and the kind of comment.
LookAt:=xlWhole requires the entire cell content to match. LookAt:=xlPart finds the search text anywhere within the cell content.
SearchOrder:=xlByRows searches across rows before moving down; xlByColumns searches down columns before moving across.
SearchDirection:=xlNext moves forward through the search order. xlPrevious searches in the reverse direction.
MatchCase:=True distinguishes uppercase and lowercase letters. With False, the search is not case-sensitive.
SearchFormat:=False avoids restricting the result to a cell format left over from a prior Find operation.

For example, with LookAt:=xlWhole, searching for “cat” does not match “catalog”. With LookAt:=xlPart, it can. A whole-cell match is often safer for identifiers, status labels, or category names; a partial match is useful for locating a phrase inside longer descriptions. If a search seems to return an unexpected result, check these options before changing the range.

Find every matching cell

Find returns one match at a time. To process every occurrence, save the first result’s address, use FindNext to continue, and stop when the search wraps around to the first address. The following example prints each matching address in the Immediate window in the VBA editor. It searches the same fixed range and uses a partial, case-insensitive match.

Sub FindAllMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A1:D100")

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

    If foundCell Is Nothing Then Exit Sub

    firstAddress = foundCell.Address
    Do
        Debug.Print foundCell.Address(False, False)
        Set foundCell = searchRange.FindNext(After:=foundCell)
        If foundCell Is Nothing Then Exit Do
    Loop While foundCell.Address <> firstAddress
End Sub

Setting After to the last cell makes the search begin at the range’s first cell; Excel begins after the specified cell and wraps through the range. The address check is essential because FindNext can wrap back to the first match. Without a stopping condition, a loop can repeatedly process the same results. Press Ctrl+G in the VBA editor to show the Immediate window on Windows, or choose View > Immediate Window in the editor.

If the macro will edit, format, or delete every match, decide whether the operation could affect later search results. For example, deleting rows while iterating can shift cells and complicate the search. A safer design may first collect the matching addresses, then make changes in a separate pass, or process rows from bottom to top when deletions are involved.

Search a whole sheet or several worksheets

To search the entire worksheet, set the range to ws.Cells rather than a smaller address. This is convenient, but can take longer than searching a known data area, especially in a large workbook. A defined range such as a table’s data body is usually more efficient and avoids matches in unrelated notes elsewhere on the sheet.

Find searches one Range object at a time; it does not automatically search every worksheet in a workbook. To search multiple sheets, loop through the worksheets and call Find separately for each one. Keep the result tied to its worksheet when reporting or using its address: the address B7 alone does not identify which worksheet contains the match. Also consider whether hidden worksheets should be included; a loop over all worksheets includes them unless the code explicitly skips them.

For formulas, choose xlFormulas when the target is part of the formula text—for instance, searching for a function name. Choose xlValues when the target is the displayed or calculated result. These are different questions: a formula such as =A1&" North" contains “North” in its formula, while its calculated value may also contain “North”. When formulas return errors or formatted numbers, test the chosen search mode on representative cells rather than assuming the displayed appearance and stored value are identical.

Common problems and how to fix them

The macro says no match exists. Confirm the text is in the specified range and check LookIn, LookAt, and MatchCase. A search for a displayed result may need xlValues, while a search for formula text needs xlFormulas.
The wrong cell is returned. Check whether partial matching is enabled and whether the search range includes other cells. Specify SearchOrder and SearchDirection so the first result is predictable.
The code errors when it uses the result. A failed search returns Nothing; test that condition before reading Address or Value.
The result depends on an earlier Find operation. Include the relevant named arguments on each call. In particular, explicitly set LookIn, LookAt, SearchOrder, SearchDirection, and MatchCase.
A search loop never ends. Store the first result’s address and stop when FindNext returns to it. A search wraps around rather than simply returning “done” after the last cell.

Another useful distinction is between a cell’s value and its formatting. Find can search by format when configured to do so, but that is not the same as searching for the text or number in a cell. Set SearchFormat:=False for ordinary content searches. If a workbook uses merged cells, unusual number formats, or formulas that return empty strings, test the macro on those specific cases because what appears on the sheet may not make the underlying cell contents obvious.

Find compared with other VBA approaches

Use Find when you need Excel’s built-in search behavior, such as exact or partial matching, case control, and a returned cell reference. It is especially handy when the data is not organized as a simple list or when the macro needs to select, inspect, or act on the matching cell.

For a small, fixed range, a VBA loop that reads each cell and compares its value can be easier to customize—for example, to apply a special comparison rule or build a list of results. For large ranges, repeatedly accessing individual cells can be slow; reading the range into a VBA array and checking the array may be more efficient, though it requires additional code to map array positions back to worksheet cells. For a one-time manual search, Excel’s Find dialog is often simpler than writing a macro. The best method depends on whether the task is interactive, repeatable, or requires custom matching.

Run macros carefully

In Microsoft 365 for Windows, open the VBA editor with Alt+F11, insert a standard module with Insert > Module, paste the macro, and run it from the editor or Excel’s Macro dialog. The Developer tab may need to be enabled to access Visual Basic and Macros from the ribbon. On Mac, Excel also supports VBA, but the editor and available shortcuts differ; use the Visual Basic command on the Developer tab or the Mac’s Tools > Macro menu where available. Excel for the web cannot create, run, or edit VBA macros; open the workbook in desktop Excel.

Save a copy of the workbook before running a macro that changes data. VBA actions generally cannot be undone with Ctrl+Z, even if the macro only makes a small edit. The examples above only display messages or print addresses, but a modified macro may overwrite cells or affect many matches quickly. If a macro is downloaded or received from someone else, inspect its code and follow your organization’s macro-security rules before enabling it.

Frequently Asked Questions

How do I find a specific cell in Excel VBA? Use Range.Find on a worksheet range, set the search options, then check whether the returned range is Nothing. For example, search ws.Range("A1:D100") for a whole-cell match using LookAt:=xlWhole.

How do I find a partial match with VBA? Set LookAt:=xlPart in the Find call. That allows a search string to match part of a cell’s content; use xlWhole when the entire cell must match.

How can I find all occurrences, not just the first? Call Find once, save the first result’s address, and continue with FindNext. Stop when the search returns to that saved address so the wraparound does not create an endless loop.

What is the difference between xlValues and xlFormulas? xlValues searches cell results, while xlFormulas searches formula content. Choose based on whether you want to find what a cell evaluates to or text contained in the formula itself.

Why does VBA Find return Nothing? The search text may not be within the specified range, or the search options may not match the data. Check the worksheet, range, whole-versus-partial setting, case setting, and whether you need to search values or formulas.

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.