excelavon

VBA Find a Value in a Column: Methods and Examples

VBA & macros · Updated November 3, 2026

VBA Find a Value in a Column: Methods and Examples

To find a value in an Excel column with VBA, use the Range.Find method on the column and specify whether the match must be exact or partial. For example, searching column A with LookAt:=xlWhole finds a cell whose entire value matches; the macro can then check whether Find returned a cell before using it.

How Range.Find searches a column

Range.Find searches a specified range and returns a Range object for the first matching cell. If nothing matches, it returns Nothing. That makes it useful when a macro needs the location of a value, such as its cell address, row number, or neighboring data.

The range determines where the search can find a result. To search all of column A, use ws.Columns("A"). To search only the data below a header, limit the range to the relevant rows, such as A2 through A500. Limiting the search can avoid matching a header and can make the intended search area clearer.

A Find search is not the same as testing whether two values are identical in every possible sense. The result depends on settings such as whether Excel looks at displayed values or formula text, whether it requires a whole-cell match, and whether it distinguishes uppercase and lowercase. State those settings in code rather than relying on whatever was used in the Find dialog previously.

A basic macro for an exact match

This example asks for a value, searches column A on a worksheet named Data, and reports the first exact match. It does not select or change the cell, so it can be adapted to other code that needs the cell reference.

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

    ' Ask for the value to find.
    searchValue = InputBox("Enter the value to find in column A:")
    If Len(CStr(searchValue)) = 0 Then Exit Sub

    ' Change Data if your worksheet has a different name.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Columns("A")

    ' Search for a whole-cell match in cell values.
    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 "No exact match was found in column A."
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
End Sub

In the example, a match in A27 produces the message “Found at A27.” The specified After cell is the last cell in the search range, so the search proceeds through the range and can wrap to its beginning. The starting cell itself is not searched until the search wraps around. For a full-column search, this still allows a match anywhere in column A.

To use the macro in Excel for Microsoft 365 on Windows, save a copy of the workbook first, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. Change Data if needed. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). On Mac, the Visual Basic Editor is available from the Developer tab; Option+F11 is a common shortcut, although some keyboards also require Fn. Excel for the web can open a macro-enabled workbook but cannot run VBA macros.

Choose exact, partial, or case-sensitive matching

The LookAt argument controls whether the search must match the whole cell or just part of it. With xlWhole, searching for North does not match “North Region.” With xlPart, that cell can match because it contains the search text. Use a whole-cell match for identifiers, codes, or names when an accidental substring could point to the wrong record.

  • LookAt:=xlWhole searches for a whole-cell match.
  • LookAt:=xlPart searches for the text anywhere within a cell.
  • MatchCase:=False treats uppercase and lowercase as equivalent.
  • MatchCase:=True makes the search case-sensitive.

For example, to find any cell in column A containing the text “North,” change the example’s LookAt argument to xlPart. To distinguish “North” from “north,” set MatchCase to True. Case sensitivity is separate from exactness: a search can be exact but case-insensitive, or partial and case-sensitive.

Excel Find also recognizes wildcard characters in searches. An asterisk can represent any sequence of characters, and a question mark can represent one character. A tilde is used to search for a literal wildcard character. If users can enter wildcard characters as ordinary data, consider whether they should be treated as search patterns or escaped as literal text; otherwise a search may match more cells than expected.

Set every important Find option

Excel retains some Find settings between searches, including settings shared with the Find dialog. A user may have chosen “Match entire cell contents” or a particular search direction in the interface, and code that omits arguments can inherit unexpected behavior. Supplying the important arguments each time makes a macro more predictable.

  • What is the value or text being searched for.
  • LookIn:=xlValues searches cell values, including the displayed results of formulas. LookIn:=xlFormulas searches formula text instead.
  • LookAt controls whole-cell versus partial matching.
  • SearchOrder specifies row-by-row or column-by-column order.
  • SearchDirection sets the direction, commonly xlNext.
  • MatchCase sets case sensitivity, and SearchFormat:=False prevents a saved format criterion from limiting the search.

For a column, SearchOrder:=xlByRows is a clear choice, even though a single-column range has only one column to traverse. The distinction matters more when the search range covers multiple columns. Pick xlValues when the question is “Which cell evaluates to this value?” Use xlFormulas when the formula itself is what matters. A formula cell displaying 25, for example, can be found by its result with a values search, while a formulas search is about the formula expression stored in the cell.

Find every matching cell, not just the first

Find returns one match. To process duplicates, use FindNext repeatedly and stop when the search returns to the first matching address. This example searches for the exact text North in column A and prints each matching address to the Immediate window in the Visual Basic Editor. Open that window with Ctrl+G on Windows.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Columns("A")

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

If North appears in A4, A19, and A35, the Immediate window lists those three addresses. The starting-address check prevents the loop from continuing indefinitely after the search wraps around. The example lists locations only; you can replace Debug.Print with an action appropriate to your task, such as reading a value from the matching row. Avoid changing the searched cells in a way that alters the search results before the loop finishes.

Use the result to retrieve related data

Once foundCell refers to a match, its properties let code use the location without selecting it. foundCell.Row gives the worksheet row number, and foundCell.Address gives its address. For example, if column A contains product IDs and column B contains descriptions, the description on the matching row is in the cell one column to the right. Check that a match exists before reading from it; otherwise, using a property on Nothing causes a runtime error.

Searching a whole column includes its header and every other cell in that column. If the header could equal the search value, start below it. A bounded range can be built from a known last row, or the macro can determine the last populated row before searching. The right approach depends on the sheet: a fixed-size table may have a known range, while a list that grows over time needs a range that adapts. If there may be no data rows, handle that case before constructing or searching the range.

Also consider which workbook the code should search. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel. They may differ if the macro is stored in another workbook, such as the Personal Macro Workbook. Use the reference that matches the intended data source rather than relying on which workbook happens to be on screen.

Common problems and how to avoid them

  • No result despite seeing the value: Check the search range, the worksheet name, and whether the value is in the searched column. Confirm that LookAt is appropriate and that LookIn searches values rather than formula text, or vice versa.
  • The wrong row is returned: A partial match may have found a longer value containing the search text. Use xlWhole when the entire cell must match, and consider whether duplicate exact matches exist.
  • A match appears only sometimes: Make all relevant Find arguments explicit. Also check the input’s data type and the cell contents; a number stored as text is not always interchangeable with a numeric value in every comparison context.
  • The macro stops with an error: Verify that the named worksheet exists and that the result is checked with If foundCell Is Nothing Then before accessing its address, row, or value.
  • The search includes a heading: Search from the first data row instead of the entire column.

Do not assume Find returns the topmost match in every situation without considering the search start point and direction. Its search wraps around the specified range, and the After argument affects where the search begins. Set those options deliberately when the order of matches matters.

When to use Find instead of another method

Use Range.Find when VBA needs to locate a cell and then work with that cell or its row. It is especially convenient for searching a large column without writing a loop that examines each cell individually. For a short list, a straightforward loop may be easier to customize—for example, when each item needs a different comparison rule—but it requires more code and careful handling of empty cells and data types.

If the goal is simply to return a related value in the worksheet, a lookup formula may be more appropriate than a macro. Excel’s XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it returns a corresponding value rather than a VBA cell object. The Find command in Excel’s interface is another option for a one-time manual search: press Ctrl+F on Windows or Command+F on Mac and use the dialog’s options to control the search. VBA is most useful when the search is part of a repeatable process or needs to trigger follow-up actions.

Frequently Asked Questions

How do I find an exact value in a column using VBA?
Search the column with Range.Find and set LookAt:=xlWhole. Then test whether the returned range is Nothing before using it.

How do I find part of a value in a column?
Set LookAt:=xlPart in the Find call. This allows a search term to match text contained within a longer cell value.

How can I find all duplicate matches?
Call Find once, save the first match’s address, and use FindNext until the search returns to that address. Check for Nothing before using each result.

Why does VBA Find return Nothing?
The target may not be in the specified range, or the search options may not match the cell contents. Check the worksheet and range, exact-versus-partial matching, and whether you need to search values or formulas.

Can VBA Find distinguish uppercase and lowercase?
Yes. Set MatchCase:=True to make the search case-sensitive. Set it to False for a case-insensitive search.

Can I undo a macro that finds or changes a value?
Excel’s Undo history does not undo VBA macro actions with Ctrl+Z. The examples above only search and report results, but save a copy of the workbook before running any macro that changes data.

Related

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.