runtime error 424 "Object Required" during finding specific name in the range-CodePudding

below script I'm running, but getting "runtime error", help needed, what is wrong?

Dim sh As Worksheet
Dim NameToFind As Variant
Dim FindRes As Range
Set sh = ThisWorkbook.Sheets("Data")
Call Find_files ' Show all files in directory in range A18:A109
Set NameToFind = sh.Range("N5") 'name stored in cell N5
Set FindRes = sh.Range("A18:A109").Find(What:=NameToFind, LookIn:=xlValues)
If FindRes Is Empty Then '---<Error is here...
   Debug.Print "no match found to " & sh.Range("N5").Value
Else
   Debug.Print "match found"
End If

CodePudding user response：

Check if FindRes is Nothing, not Empty.

Dim sh As Worksheet
Dim NameToFind As Variant
Dim FindRes As Range
Set sh = ThisWorkbook.Sheets("Data")
Call Find_files ' Show all files in directory in range A18:A109
Set NameToFind = sh.Range("N5") 'name stored in cell N5
Set FindRes = sh.Range("A18:A109").Find(What:=NameToFind, LookIn:=xlValues)
If FindRes Is Nothing Then 
   Debug.Print "no match found to " & sh.Range("N5").Value
Else
   Debug.Print "match found"
End If