Excel VBA Find Value in Range: Examples and Tips

To find a value in an Excel range with VBA, use the range’s Find method, then check whether it returned a cell or Nothing. For a reliable search, specify options such as exact or partial matching and whether to search values or formulas; Excel remembers some Find settings between searches.

Basic VBA code to find one value

This example searches cells A2:A100 on a worksheet named Data for the exact value P-1042. If found, it displays the cell address and value; otherwise, it reports that there was no match.

Sub FindOneValue()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    searchValue = "P-1042"

    ' Search cell values and require a whole-cell match.
    Set foundCell = searchRange.Find(What:=searchValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Value not found."
    Else
        MsgBox "Found at " & foundCell.Address(False, False) & _
            ": " & foundCell.Value
    End If
End Sub

Change the worksheet name, range, or searchValue to suit your workbook. ThisWorkbook means the workbook containing the VBA code, which avoids accidentally searching whichever workbook happens to be active. If you intend to search the active workbook instead, use ActiveWorkbook deliberately. The After argument points to the last cell in the search range, so the search begins at the range’s first cell and proceeds in the chosen direction.

What Range.Find does

Range.Find searches a specified range and returns a Range object for the first matching cell. It does not return the cell’s value by itself: use the returned object’s properties, such as .Value, .Address, or .Row, to inspect or use the match. If there is no match, the returned object is Nothing. Always test for that before trying to read a property, or the macro can stop with an object-related error.

The search settings determine what Excel considers a match and how it traverses the cells:

What is the text, number, date, or other value to look for.
LookIn chooses between cell values (xlValues), formulas (xlFormulas), or comments/notes in versions that support those options.
LookAt sets whole-cell matching (xlWhole) or matching within cell contents (xlPart).
SearchOrder selects row-by-row (xlByRows) or column-by-column (xlByColumns) traversal.
SearchDirection controls forward (xlNext) or backward (xlPrevious) searching.
MatchCase determines whether letter case matters. The example sets it to False.

Supplying the important arguments each time is more dependable than relying on Excel’s defaults. The Find dialog and VBA share certain search settings, so an earlier manual search can affect a later macro if the macro leaves an option unspecified.

Choose exact, partial, or wildcard matching

For an exact whole-cell match, use LookAt:=xlWhole. A search for “North” then matches a cell containing North, but not one containing Northern. Set LookAt:=xlPart when the target may be included within longer text; that same search can then match both examples. The result is still the first matching cell in the search order, not every matching cell.

Excel Find also recognizes wildcard characters in text searches. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. For example, a search for AB* can match text beginning with AB. To search for a literal wildcard character, precede it with a tilde: ~* searches for an asterisk. Wildcards are useful for variable codes or names, but can create surprising matches if the search string contains them unintentionally.

Case-insensitive searching is the usual setting for labels and names. Set MatchCase:=True only when uppercase and lowercase forms must be treated as different. If you search a numeric or date value, ensure that the search value has the same underlying type as the cells. A date displayed as 2-Nov-2026 is stored as a number in Excel; searching for a text string that merely looks like that date may not behave like searching for the actual date value.

Find every matching cell

To process all matches, call Find once, remember the first match’s address, and repeatedly call FindNext. Excel wraps around to the beginning of the range after reaching its end, so the loop must stop when it returns to the first match. This example prints matching cell addresses and values to the VBA Immediate window (open it in the editor with Ctrl+G on Windows).

Sub FindAllValues()
    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("A2:A100")

    ' Use the same search options on every run.
    Set foundCell = searchRange.Find(What:="P-1042", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

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

The loop does nothing if the first search finds no result. If you want to change matching options, make that change in the initial Find call; FindNext continues using the established search. You can replace Debug.Print with code that writes results to another range, but plan for where those results should go so the macro does not overwrite source data.

Search displayed values or formulas

Use LookIn:=xlValues when the goal is to find the result stored or calculated in a cell. This is generally appropriate for searching labels, entered numbers, and formula results. Use LookIn:=xlFormulas when you need to search the formula text itself—for example, to locate formulas that contain a particular reference or function name. A formula cell can therefore be found based on its displayed result in one search and its formula expression in another.

Formatting can affect what a value looks like without changing the value itself. A number formatted with leading zeros, for example, may display like an ID even though its underlying value is numeric. If a search misses something that appears on screen, check the cell’s formula bar and number format, then choose an appropriate LookIn setting and search value. For identifiers that must preserve leading zeros, storing them as text may be more suitable than treating them as numbers.

Common problems and practical checks

The macro errors after a failed search: test If foundCell Is Nothing Then before accessing foundCell.Value or another property.
The wrong occurrence is returned: confirm whether the search should be whole-cell or partial, and set the search order and direction explicitly.
The result changes after using Find in Excel: include the relevant arguments in VBA because Find settings can persist from the dialog or previous searches.
A visible match is skipped: check for a number-versus-text mismatch, a formula result, wildcard characters, or an unexpected space in the cell.
Only some columns are searched: verify that the range variable covers the intended cells and worksheet. A search of A2:A100 will not examine column B.

Use a clearly bounded range when possible. Searching a whole worksheet can take longer and may find an unintended match far below the visible data. If the data is an Excel Table, a table column or its DataBodyRange can make the target more precise, though a table with no data rows needs a check before using that range. For merged cells or unusual layouts, test the macro on a copy and confirm which cell address Excel returns.

When Find is not the best choice

Use Find when VBA needs a matching cell object—for example, to select its row, read a neighboring cell, or update that cell. If you only need to know whether a value exists, a worksheet counting function or a VBA loop may be simpler. If you need to return a related value from another column, a lookup formula or VBA lookup approach may better express the task. For a macro that needs to examine many cells, reading the range into a VBA array and comparing values in memory can be faster than interacting with worksheet cells one by one, although it requires more code and care with data types.

Unlike a formula, a VBA search runs only when its macro is run (or called by other code); it does not automatically recalculate as the source data changes. Choose based on the desired result: a reusable visible result often belongs in a worksheet formula, while a one-time action or workflow belongs in VBA. Excel 365 functions such as XLOOKUP can be useful for formula-based lookups, but they do not replace Find when the macro needs the address of a matching cell.

Run the macro safely

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure, and run it with F5 while the cursor is inside the macro. The workbook must be saved in a macro-enabled format such as .xlsm to retain VBA code, and macros must be allowed by the workbook’s security settings. On Mac, use Option+F11 (on some keyboards, also hold Fn) to open the VBA editor; the editor and shortcut behavior can differ by keyboard and Excel setup. Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros; use the desktop Excel app for those tasks.

Save a copy of the workbook before running a macro that changes data. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the practical way to recover from an unintended edit. The examples above only display or print search results, but adapting them to change cells makes that precaution important.

Frequently Asked Questions

How do I find an exact match in VBA? Set LookAt:=xlWhole in the range’s Find call, then check whether the returned range is Nothing. Also set the other search arguments you rely on so earlier Find settings do not change the result.

How do I find part of a cell’s text? Use LookAt:=xlPart. For pattern-based searches, Find also supports * and ?; use a tilde to search for those characters literally.

What does Find return when there is no match? It returns Nothing. Test that condition with If foundCell Is Nothing Then before reading the cell’s address, value, or row.

How can I find all matches instead of the first one? Start with Find, save the first result’s address, and continue with FindNext until the search returns to that address. The all-matches example above also handles the no-match case.

Can VBA Find search formulas? Yes. Set LookIn:=xlFormulas to search formula expressions, or use LookIn:=xlValues to search cell values and calculated results, depending on what you need to match.

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.