How to use the RANGE.FIND method

The Range.Find method returns a range object for the first cell that matches the lookup value.

Macro used in workbook above

Sub Macro1()

MsgBox Range("B3:B8").Find(Range("D3")).Address

End Sub

The macro above uses the Range.Find method to search cell range B3:B8 for the lookup value in cell D3. A message box shows the address of the found cell.

1. Range.Find Syntax

expression.Find( What , After , LookIn , LookAt , SearchOrder, SearchDirection , MatchCase , MatchByte , SearchFormat )

Back to top

2. Range.find Arguments

Back to top

3. Range.Find Comments

The Find method is not going to change the selection or the active cell, it only returns the range object of the found value.

The LookIn, LookAt, SearchOrder, and MatchByte are automatically saved each time you use the Find method. The saved values are used if don't specify these arguments. The Find dialog box changes these settings as well and vice versa meaning the Find method changes the Find dialog box settings. Make sure you specify these arguments each time you use the Find method to prevent unpredictable behavior.

It also looks like that the what argument is also saved to the Find dialog box.

Back to top

4. Example 1 - Value not found

The macro returns an error if the value is not found. Run-Time error '91': Object variable or With block variable not set. To handle the error see the following macro.

Sub Macro2()

If Range("B3:B8").Find(Range("D3")) Is Nothing Then
    MsgBox "Value not found"
Else
    MsgBox Range("B3:B8").Find(Range("D3")).Address
End If

End Sub

Back to top

5. Example 2 - Find multiple values

The following macro iterates through each found value and saves the cell address to a variable, the msgbox then shows the variable.

Sub Macro3()
With Range("B3:B8")
    If .Find(Range("D3")) Is Nothing Then
        MsgBox "Value not found"
    Else
        Set a = .Find(Range("D3"))
        Set b = a
        c = a.Address
        Do While Not .FindNext(b) Is Nothing And a.Address <> .FindNext(b).Address
            c = c & vbNewLine & .FindNext(b).Address
            Set b = .FindNext(b)
        Loop
    End If
End With
MsgBox c
End Sub

Back to top

6. Example 3 - Return adjacent value

The image above demonstrates the macro below, it saves the adjacent values to each found value in cell range B3:B8 using the lookup value in cell E3 to a variable. The msgbox then shows the contents of the variable.

Sub Macro4()
With Range("B3:B8")
    If .Find(Range("E3")) Is Nothing Then
        MsgBox "Value not found"
    Else
        Set a = .Find(Range("E3"))
        Set b = a
        c = a.Offset(, 1).Value
        Do While Not .FindNext(b) Is Nothing And a.Address <> .FindNext(b).Address
            c = c & vbNewLine & .FindNext(b).Offset(, 1).Value
            Set b = .FindNext(b)
        Loop
    End If
End With
MsgBox c
End Sub

Back to top

Get Excel *.xlsm

How to use the Range.Find method.xlsm

Back to top

RANGE.FIND method examples

Consolidate sheets [vba]
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

How to use the RANGE.FIND method
The Range.Find method returns a range object for the first cell that matches the lookup value. Macro used in workbook above Sub […]

Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Search all workbooks in a folder and sub folders – VBA
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]

Select and view invoice [VBA]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.

Excel categories

Comments (1)