find if `find` method returns `nothing` in excel vba
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:
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.
DBWeinstein
Co-Founder at HomeKeepr. Coding hobbyist. Always learning about everything.
Updated on March 16, 2020Comments
-
DBWeinstein over 4 years
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 lineIf found1 = Nothing Then
with the wordNothing
highlighted. "Compile error; Invalid Use of Object". Not sure what to do.