Excel VBA: Find a Value in a Range and Select Its Cell

In Excel VBA, use Range.Find to locate a value, check whether it returned a cell, and then activate the worksheet and select that cell. Supplying the search options explicitly makes the result more predictable than relying on Excel’s saved Find settings.

The basic approach

Find searches a specified range and returns a Range object for a match. If it cannot find the value, it returns Nothing. The safe sequence is therefore: define the worksheet and search range, call Find, test the result, and select it only when a match exists.

For example, a macro can look for the exact text North in cells A2:A100 on a worksheet named Data. If it finds a match, it selects the matching cell. If there is no match, it displays a message rather than causing an error by trying to select an empty result.

Selection is a visible interface action: Excel moves the active cell to the match. It is different from simply storing the result in a VBA variable, which is often preferable when another part of a macro needs to read or change the found cell without moving the user’s selection.

A reliable VBA example

Open the VBA editor in desktop Excel, insert the code into a standard module, and adjust the worksheet name, search range, and search value for your workbook. This example searches evaluated cell values and requires a whole-cell match, without distinguishing uppercase and lowercase.

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

    ' Set the worksheet, range, and value to search for.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    searchValue = "North"

    ' Search from the end so the first cell is included in the search.
    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.", vbInformation
    Else
        ws.Activate
        foundCell.Select
    End If
End Sub

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, even when the macro appears to make only a small change. This example selects a cell rather than editing one, but a backup is a sensible precaution when adapting code for a larger task.

What the Find arguments mean

The arguments in the example control important parts of the search. Setting them explicitly avoids surprises because Excel can retain some Find options from earlier searches in the user interface or VBA.

What is the value or text to locate. It is held in searchValue here so you can replace the value without rewriting the search call.
After identifies the cell after which Excel begins searching. The example starts after the range’s last cell, so Excel wraps around and can examine the first cell too.
LookIn:=xlValues searches evaluated values. Use xlFormulas when the formula text itself is what you need to find, such as searching formulas for a reference.
LookAt:=xlWhole requires the entire cell contents to match. xlPart allows a match within longer cell contents.
SearchOrder:=xlByRows searches across rows before proceeding to the next row. For a two-dimensional range, xlByColumns changes the order.
SearchDirection:=xlNext moves forward through the search order. Because the search wraps around, the starting point affects which duplicate is returned first.
MatchCase:=False makes the text search case-insensitive. Change it to True if uppercase and lowercase must be treated differently.
SearchFormat:=False prevents a previously configured cell format from restricting the search.

For a single-column range such as A2:A100, row order is straightforward. The distinction between row and column order matters more when searching a rectangular block. If the range contains multiple matches, Find returns one match according to its starting point and search settings; it does not automatically select every matching cell.

Exact matches, partial matches, and data types

Choose xlWhole when the cell should equal the search value. If A5 contains North Region, a whole-cell search for North will not match it. Changing the setting to xlPart will find North within North Region. That can be useful for names or descriptions, but it may also return an unintended result when the search text appears inside a longer word.

Excel Find also recognizes wildcard characters in text searches. An asterisk represents a sequence of characters and a question mark represents one character. If you need to search for a literal wildcard character, precede it with a tilde; for example, a tilde can escape an asterisk. Wildcards are most relevant to partial text searches, so use a whole-cell match when that better expresses the requirement.

Consider the type of data being searched. A date stored as an Excel date value is represented internally as a number, even though the cell displays a date. Searching with a VBA Date value is usually clearer than searching for a formatted date string. Similarly, a number stored as text may not behave like the same number stored as a numeric value. If a search unexpectedly fails, check whether the source cells contain numbers, text, formulas, or dates, rather than assuming the displayed appearance tells the whole story.

Choosing the right range and workbook

Use the narrowest range that can contain the answer. Searching an entire worksheet can take longer and may find a value in an unrelated area. A range such as ws.Range("A2:A100") is easier to reason about than an unqualified Range("A:A"), especially in a workbook with headers, notes, or other tables.

The example uses ThisWorkbook, which refers to the workbook containing the VBA code. That is appropriate when the macro is stored in the workbook it should search. If the code is in a personal macro workbook or another file and the target is the workbook currently in use, you may need to refer to ActiveWorkbook instead. Be deliberate: active-workbook references can point to a different file if the user switches workbooks.

Keep the worksheet reference attached to the range, as in ws.Range("A2:A100"). An unqualified Range can refer to the active sheet, which may not be the sheet you intended. Once a match is found, activating ws before selecting the cell makes the intended sheet active. Excel cannot select a cell on an inactive worksheet without first activating that worksheet.

Common problems and how to fix them

Object variable or With block variable not set: This often happens when code uses foundCell.Select without checking whether Find returned Nothing. Keep the If foundCell Is Nothing test.
The wrong duplicate is selected: Multiple cells may contain the same value. Adjust the search range or starting point, or use a loop with FindNext if the task needs to inspect all matches.
A formula result is found—or missed—unexpectedly: Choose xlValues to search evaluated results or xlFormulas to search formula text. A cell displaying North might contain a formula that returns North, so these settings represent different searches.
The result changes after using Excel’s Find dialog: Make all relevant Find arguments explicit. This reduces dependence on options saved from earlier searches.
The macro cannot select the result: Confirm the target sheet exists and can be activated. Sheet protection settings can also restrict which cells a user may select. If the task only needs the result’s value or address, avoid selection and work with foundCell directly.

For a multi-match task, record the first match’s address, then continue with FindNext until the search returns to that address. This stopping condition matters because Find searches wrap around; a loop that does not detect the first match again can continue indefinitely. When searching duplicates, also decide what the macro should do with each result—report its address, count it, or process it—rather than repeatedly selecting cells without a clear purpose.

Selecting a cell versus using the result directly

Use Select when a person should see the matching cell become active. For automation, the returned Range is usually more useful than selection. For example, code can read foundCell.Value, inspect foundCell.Address, or write to that cell without changing the active selection. This makes a macro less disruptive and reduces dependence on which sheet or cell was active before it ran.

Selection is also different from highlighting. Selecting a cell changes Excel’s active cell and selection; it does not apply a fill color or other lasting formatting. If the goal is to mark every matching value visually, conditional formatting or a separate formatting routine may be a better fit. If the goal is simply to retrieve a related value from another column, a lookup approach may be more appropriate than moving the user’s cursor.

Excel version and running the macro

VBA macros run in the desktop versions of Excel, including Microsoft 365 for Windows and Mac. Excel for the web can open and edit many workbooks but does not run VBA macros. To run this code, use desktop Excel and save the file in a macro-enabled format such as .xlsm; a standard .xlsx file does not store VBA code.

On Windows, open the Visual Basic Editor with Alt+F11. On Mac, use Tools > Macro > Visual Basic Editor; keyboard shortcuts can differ by keyboard and system settings. You may need to show the Developer tab to access macro tools from the ribbon. Only enable macros in files you trust, and follow your organization’s security rules. If the workbook opens with macros disabled, Excel’s security settings—not the search code—may be preventing the macro from running.

Frequently Asked Questions

How do I select the first matching cell in a range?
Call Find on the range, check that the returned object is not Nothing, activate the worksheet, and select the returned cell. Set the search options explicitly to define what counts as a match.

Why does VBA Find return Nothing when I can see the value?
Check whether the code searches the correct worksheet and range, whether it uses xlValues or xlFormulas appropriately, and whether whole-cell matching is too strict. Also check for text-versus-number differences and wildcard characters.

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

How do I find a value when it is only part of a cell?
Set LookAt:=xlPart. For example, a partial search for North can match North Region. Use this only when a substring match is intended, since it can also match longer or unrelated text.

Can Excel for the web run this VBA macro?
No. Use desktop Excel for Windows or Mac to run VBA. Excel for the web can work with many workbook contents, but VBA macro execution requires the desktop application.

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.