VBA Find a Value in a Range: Excel Code and Examples

In VBA, use Excel’s Range.Find method to search a range and return the matching cell; check whether the result is Nothing before using it. The example below searches for an exact value in a specified worksheet range and reports the cell address.

Basic VBA Find example

Sub FindOneValue()
    Dim searchRange As Range
    Dim foundCell As Range
    Dim lookupValue As Variant

    ' Set the value and range to search.
    lookupValue = "M-204"
    Set searchRange = ThisWorkbook.Worksheets("Data").Range("A2:A100")

    ' Use explicit options so the search is predictable.
    Set foundCell = searchRange.Find(What:=lookupValue, _
        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.Worksheet.Name & "!" & _
            foundCell.Address(False, False)
    End If
End Sub

Change the worksheet name, range, and lookup value to fit your workbook. This example searches cells A2 through A100 on a sheet named Data. If it finds M-204, the message identifies the sheet and cell, such as Data!A17. If there is no match, it displays a not-found message rather than trying to read an address from an empty result.

The range is deliberately qualified through ThisWorkbook, which means the workbook containing the macro. That avoids accidentally searching a different workbook just because it is active. If the data is in another workbook, qualify the range with that workbook object instead. The worksheet name in the example must match the sheet tab exactly.

How Range.Find works

Find searches the cells in the range and returns a Range object for the first match it encounters. It does not return the cell’s value by itself: once you have a result, you can inspect its address, value, row, column, or other properties. If no cell matches, the result is Nothing. That is why the example tests foundCell before referring to its address.

Excel’s Find dialog and VBA searches share some search settings. Options such as whether to look in formulas or values, and whether to match a whole cell or part of one, can be remembered from a previous search. Supplying the options explicitly in VBA avoids relying on whatever a user last chose in the dialog. It also makes the intended search easier to understand when someone revisits the macro.

The After argument tells Excel where to begin relative to the search range. In the example, it is the range’s last cell, so searching forward begins at the first cell and proceeds by rows. Find wraps around at the end of the range. For a single result that is usually convenient; for every match, account for that wraparound so the macro knows when it has returned to its starting match.

SearchOrder controls whether Excel proceeds by rows or by columns, while SearchDirection selects forward or reverse search. These choices matter most when searching a two-dimensional block and more than one cell matches. They do not change which cells are eligible; the range passed to Find defines the search area.

Exact, partial, and case-sensitive matches

The example uses xlWhole, which requires the cell contents to match the search value as a whole. If a cell contains M-204-A, searching for M-204 with that setting will not find it. Use xlPart when the value can appear within longer text, such as searching for the word “invoice” in notes that contain additional words. Partial matching is useful for exploratory searches, but can return unintended cells if the search text is common.

MatchCase is False in the sample, so uppercase and lowercase differences are ignored for text. Set it to True when letter case is meaningful to your data. Find does not offer a separate setting to ignore leading or trailing spaces; a cell containing “M-204 ” may therefore fail an exact match against “M-204”. If entries are inconsistently spaced, clean the data or use a separate comparison approach that trims text.

Excel Find also supports wildcard characters in the What value. An asterisk represents any number of characters, and a question mark represents one character. To search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde. Wildcards can help with patterns such as product codes, but they make the search less specific, so verify that the result is the intended record.

Searching displayed values or formulas

LookIn determines what Excel searches inside each cell. xlValues searches cell values, including the results currently shown by formulas. xlFormulas searches formula content instead. For example, if B2 contains a formula that calculates the text “Complete”, a value search can locate the displayed result, while a formula search is appropriate when looking for formula text such as a reference or function name. Choose based on whether the question is about what the cell shows or how it is calculated.

Formatting can affect what a value search appears to match. A date is stored as a number and displayed according to its number format, and a number shown with rounded decimals may have a more precise underlying value. If a search for a date or number fails, check the actual cell value and its type rather than assuming the visible text is the stored content. Text that looks like a number can also differ from a numeric value.

For a normal lookup against a worksheet’s displayed results, xlValues is often the right choice. For finding a formula pattern in cells, use xlFormulas. If you are searching comments, Excel has separate Find settings for comments and threaded comments in supported versions, but those are different from searching ordinary cell contents.

Finding every occurrence

Find returns one match at a time. To process all matches, save the first match’s address, then call FindNext until the search wraps back to that address. The address check prevents an endless loop when there are multiple results. This version prints each matching address to the Immediate window in the Visual Basic Editor; it does not alter the worksheet.

Sub FindAllValues()
    Dim searchRange As Range
    Dim foundCell As Range
    Dim lookupValue As Variant
    Dim firstAddress As String

    ' Set the value and range to search.
    lookupValue = "M-204"
    Set searchRange = ThisWorkbook.Worksheets("Data").Range("A2:A100")

    Set foundCell = searchRange.Find(What:=lookupValue, _
        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.Worksheet.Name & "!" & _
                foundCell.Address(False, False)
            Set foundCell = searchRange.FindNext(After:=foundCell)
            If foundCell Is Nothing Then Exit Do
        Loop While foundCell.Address <> firstAddress
    End If
End Sub

Open the Immediate window in the Visual Basic Editor to see the printed addresses. FindNext continues with the search criteria from the preceding Find call, so set those criteria explicitly in the initial call. If your goal is to collect results elsewhere, add that action inside the loop and decide how duplicate or overlapping matches should be handled.

Choosing Find or another lookup method

Find is a good fit when VBA needs to locate a cell, inspect its location, or iterate through several matches. It is also useful when the search area is not a simple lookup column. If you only need to return a corresponding value from a table, a worksheet lookup formula may be clearer and easier for other spreadsheet users to maintain.

For code that checks a known position, direct cell comparison can be simpler than searching. For example, if a macro already knows which row to inspect, reading that cell and comparing its value avoids a range-wide search. Conversely, looping through every cell can be appropriate when you need custom rules such as trimming spaces or applying a specialized comparison, but it usually requires more code than Find.

Choose based on the outcome you need:

Use Find to get the matching cell or search multiple occurrences.
Use a worksheet lookup formula when the result should remain visible and update with worksheet data.
Use a direct comparison when the target cell or row is already known.
Use a controlled loop when matches require custom normalization or validation.

Common problems and fixes

Object variable not set: The search returned Nothing and the code tried to read a property such as Address. Add a Nothing check before using the result.
The expected cell is skipped: Review After, SearchDirection, and the range boundaries. Find begins after the After cell and wraps, so its starting point may surprise code that assumes it starts at the top-left.
It finds only part of a value, or none at all: Check whether LookAt is xlPart or xlWhole. Also confirm that the cell contains the data type and text you expect.
Results change after using Find manually: A remembered dialog setting may be influencing an incomplete VBA call. Specify LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, and SearchFormat on the Find call.
A search for a number or date fails: Check for numbers stored as text, hidden decimal precision, or date serial values. A visually identical display does not guarantee identical underlying values.

Also make sure the range is not accidentally limited to the active sheet or selection. Fully qualifying the workbook, worksheet, and range makes a macro more reliable, especially when users have several workbooks open. If the range should expand as new records are added, consider using an Excel Table and referring to its data range rather than hard-coding an endpoint such as row 100.

Running the macro safely

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even if a later version of the macro changes or deletes worksheet data. The examples here only display or print results, but this precaution is important when adapting them to edit cells.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module and paste the code into the module. Run the selected procedure from the editor. On a Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. Excel for the web can open workbooks containing macros but cannot create, edit, or run VBA macros, so use the desktop Excel application.

Frequently Asked Questions

How do I search an entire worksheet? Set the search range to the worksheet’s Cells collection instead of a specific column or block. This can search a very large area, so a targeted range or table column is usually faster and less likely to match unrelated content.

How do I make VBA Find case-sensitive? Set MatchCase to True in the Find call. This affects text searches; it does not trim spaces or change the data stored in a cell.

How can I find a partial match? Set LookAt to xlPart to match the search text wherever it appears within a cell. Use xlWhole for an exact whole-cell match, and consider wildcard characters if you need to search a pattern.

Does Find return the matching value? Find returns a Range object for the matching cell. Read that cell’s Value or Value2 property when you need its contents, or use its Address, Row, or Column when you need its location.

Can I use VBA Find in Excel for the web? No. The web version does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and run the macro.

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.