find if `find` method returns `nothing` in excel vba

Question

I'm trying to find an id in a list and get it's address, but also deal with a situation if nothing is found.

Here's what I have:

Function find_in_two_ranges_two_sheets(ws1 As String, col1 As Integer) As Range

    Dim rows1 As Integer
    rows1 = Get_Rows_Generic(ws1, 1)
    Dim range1 As Range ' range of first search
    With Worksheets(ws1)
        Set range1 = .Range(.Cells(1, col1), .Cells(rows1, col1))
    End With

    Dim found1 As Range
    Set found1 = range1.Find("test id", LookIn:=xlValues)  

    If found1 = Nothing Then
        MsgBox "nothing"
    Else
        MsgBox found1.AddressLocal
    End If


    Set find_in_two_ranges_two_sheets = range1
End Function


Sub test_stuff()
    Dim x As Range
    Set x = find_in_two_ranges_two_sheets("usersFullOutput.csv", 1)
    MsgBox x.Address
End Sub

When I run test_stuff() I get an error in the function in the line If found1 = Nothing Then with the word Nothing highlighted. "Compile error; Invalid Use of Object". Not sure what to do.

try `If found1 is Nothing Then` – tospig Mar 15 '15 at 22:05 — tospig, Mar 15 '15 at 22:05

score 15 · Accepted Answer · answered Mar 15 '15 at 22:20

To check the range object you need to use is instead of =:

If found1 Is Nothing Then
    MsgBox "nothing"
Else
    MsgBox found1.AddressLocal
End If

Explanation:

Taken from Allen Browne

Nothing is the uninitialized state of an object variable. An object cannot be a simple variable such as a number or a string, so it can never be 0 or "". It must be a more comprehensive structure (a text box, form, recordset, querydef, ...)

Since it is not a simple value, you cannot test if it is equal to something. VBA has an Is keyword that you use.

find if `find` method returns `nothing` in excel vba

1 Answers1

Linked