excelavon

VBA Find If: Check Whether a Value Exists in Excel

VBA & macros · Updated November 3, 2026

VBA Find If: Check Whether a Value Exists in Excel

In Excel VBA, use the Range.Find method to look for a value, then test whether the returned range is Nothing: If foundCell Is Nothing Then means no match was found. If it is not Nothing, the variable refers to the matching cell.

The basic VBA Find and If pattern

Find searches a range and returns a Range object when it finds a match. When there is no match, it returns the special object value Nothing. Because the result is an object, use Is Nothing to check it; do not compare it with an empty string or zero.

This complete example checks cells A2:A100 on Sheet1 for an exact cell value of North:

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

    ' Change Sheet1 and the range to match your workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")

    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 foundCell Is Nothing Then
        MsgBox "North was not found."
    Else
        MsgBox "North was found in " & foundCell.Address(False, False) & "."
    End If
End Sub

Before running a macro, save a copy of the workbook: VBA actions generally cannot be reversed with Ctrl+Z. Store a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm). Run macros only when you trust their source.

What Range.Find returns and how it searches

The method belongs to a range, so the range before .Find determines where Excel looks. In the example, cells outside A2:A100 are not searched. The result is the first matching cell encountered in the chosen search order, not a list of every match. You can read or change that cell through the returned range, for example with foundCell.Value or foundCell.Address.

The After argument sets the cell after which the search begins. Excel starts beyond that cell, then wraps around within the range. Setting it to the range’s last cell makes the first cell the first candidate. A search can therefore find a match even when it is near the beginning of the range.

By default, a search can wrap to the beginning after reaching the end. If there is no match, Find returns Nothing; it does not return an empty range. The If statement must check for that result before code tries to read a property such as Address. Otherwise, referencing a missing object can cause a runtime error.

Choose exact or partial matching

The LookAt argument determines whether the entire cell must match or whether the search text can appear within the cell:

  • LookAt:=xlWhole requires the whole cell content to match. Searching for North will not match Northwest.
  • LookAt:=xlPart accepts a match anywhere in the cell. Searching for North can match both North and Northwest.

The example uses MatchCase:=False, so capitalization does not have to match. Set it to True if North and north should count as different text. Wildcards can also be used in a text search: an asterisk represents any number of characters and a question mark represents one character. Use a tilde to search for a literal wildcard character. Wildcards are not a substitute for choosing the correct LookAt setting.

Set Find arguments explicitly

Excel remembers some Find settings between VBA calls and the Find dialog. A user who previously chose to search formulas, match case, or search by columns can affect the behavior of a later call that leaves those options unspecified. For predictable results, provide the relevant arguments each time rather than relying on the dialog’s current state.

  • What is the value or text to find.
  • LookIn:=xlValues searches cell values, including the displayed results of formulas. LookIn:=xlFormulas searches formula content instead.
  • SearchOrder:=xlByRows searches across rows; xlByColumns searches down columns.
  • SearchDirection:=xlNext searches forward through that order; xlPrevious searches in reverse.
  • SearchFormat:=False avoids restricting the search according to formatting left in Find settings.

For example, if a cell contains the formula =A1&" "&B1 and displays North Region, searching with LookIn:=xlValues looks for the displayed result. Searching with LookIn:=xlFormulas looks at the formula text. Which setting is right depends on whether you want to find what a user sees or the underlying formula.

Find every matching cell

A single call to Find returns one match. To process all matches, call FindNext repeatedly and stop when the search returns to the first matching address. This example prints the addresses of every cell in A2:A100 whose entire value is North:

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

    ' Change the worksheet and search range as needed.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")

    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 foundCell Is Nothing Then
        MsgBox "No matches were found."
        Exit Sub
    End If

    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

The addresses appear in the VBA editor’s Immediate window, which you can show with View > Immediate Window. The loop remembers the first address because a search can wrap around and otherwise keep finding the same cells indefinitely. For large ranges, avoid doing substantial work inside the loop unless you need to process every match.

Common errors and practical checks

Several small details account for many Find-related problems. Check these before assuming the method is broken:

  • Wrong worksheet or range: qualify the range with a worksheet, as in ws.Range("A2:A100"). An unqualified Range refers to the active sheet, which might not be the sheet you intended.
  • Wrong match type: xlWhole will not find a word embedded in a longer cell value. Use xlPart when that is the desired behavior.
  • Formula versus result: choose xlFormulas or xlValues deliberately, especially when searching cells containing formulas.
  • Unexpected capitalization: check whether MatchCase is set as required.
  • Old Find settings: specify the search arguments explicitly, including SearchFormat, to reduce surprises from previous searches.
  • Invalid sheet name: if the named worksheet does not exist in ThisWorkbook, the macro errors before it reaches the search. Confirm the tab name or adjust the code.

When debugging, step through the procedure and inspect foundCell after the Find call. If it is not Nothing, inspect its address and value. If it is Nothing, verify the range, search text, and LookIn and LookAt choices. A value that looks identical on screen may contain extra spaces or may be a formula result, so the underlying cell content can matter.

When to use Find instead of other methods

Use Find when you need the matching cell itself: for example, to return its address, read a neighboring cell, or format the match. For a straightforward yes-or-no test, a worksheet function such as COUNTIF may be simpler, but it returns a count rather than a cell reference. In VBA, you can also loop through cells and compare their values; that approach gives detailed control over each comparison but usually requires more code than Find.

These approaches answer slightly different questions. A count can tell you whether at least one exact value exists, while Find can locate one matching cell quickly and let your procedure act on it. A loop is useful when matching rules are more complicated than Find’s options, such as a custom test involving several columns. Choose based on whether you need a count, a location, or custom comparison logic.

VBA macros run in desktop Excel for Windows and Mac, though platform-specific features and file paths can require changes. Excel for the web does not run VBA macros; opening a workbook there does not execute this procedure. The examples use standard Excel object-model methods and worksheet ranges, but they should be run from desktop Excel.

Frequently Asked Questions

How do I check if Find found something in VBA?
Assign the result to a Range variable, then use If foundCell Is Nothing Then to handle no match. Use the Else branch for a successful match.

Does VBA Find return Nothing when there is no match?
Yes. The method returns a matching Range object or Nothing when it cannot find the requested content in the specified range.

How do I find part of a cell’s text?
Set LookAt:=xlPart. Use xlWhole when the entire cell must match the search text.

Why does VBA Find return the wrong match?
Check the search range, LookIn, LookAt, case sensitivity, and other Find arguments. Excel can retain search settings from earlier Find operations, so specify the arguments you depend on.

Can Find return every matching cell?
A single Find call returns one match. Use FindNext in a loop and stop when it reaches the first match again, as shown above.

Can I run VBA Find in Excel for the web?
No. The Excel desktop applications support VBA, but Excel for the web does not run VBA macros. Use desktop Excel to run these examples.

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.