How to fix the error using Find function in Excel

Question

I tried to search a value from another sheet rather using the .FIND function

Private Function Search(rng As Range, FindString As String) As Range
    With rng
        Set Search = .Find(what:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        lookat:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
     End With

The funny thing is that if I enter

Search(Range("'DataSheet'!A1:Z1000"),"STARTING") 

It works. When I try

 SearchString = "'" & Selected_sheet &"'!A1:Z1000"

It does not work Selected_sheet is just a name of a worksheet (In this case, it is "DataSheet"). Using

Search(Range(SearchString ),"STARTING") 

Error: RUN-TIME ERROR '91'
Object variable or With Block Varibale Not Set

What could be the actual problem?

Answer 1

Your method is not incorrect but I recommend not passing your range like that.

Use this

Search(Sheets("DataSheet").Range("A1:Z1000"),"STARTING")

or

Selected_sheet = "DataSheet"
Search(Sheets(Selected_sheet).Range("A1:Z1000"),"STARTING")

BTW there is nothing wrong with Range(SearchString) in your code. It should work. How are you calling Search? Hope like this?

Dim Ret As Range '<~~~

Selected_sheet = "DataSheet"
SearchString = "'" & Selected_sheet & "'!A1:Z1000"
Set Ret = Search(Range(SearchString), "STARTING")

Reason: The function returns a Range so you cannot just call it without Set Ret = ... Where Ret is again declared as a Range.

EDIT

You can also get that error if your Search function doesn't return a range and you try using that Ret. See this example.

Dim Ret As Range

Selected_sheet = "DataSheet"
SearchString = "'" & Selected_sheet & "'!A1:Z1000"
Set Ret = Search(Range(SearchString), "STARTING")

'<~~ This will give you RUN-TIME ERROR '91' if "STARTING" is not found
Debug.Print Ret.Address

So how do we tackle this

Try this

Dim Ret As Range

Selected_sheet = "DataSheet"
SearchString = "'" & Selected_sheet & "'!A1:Z1000"
Set Ret = Search(Range(SearchString), "STARTING")

If Not Ret Is Nothing Then
    Debug.Print Ret.Address
Else
    Debug.Print "Search Didn't return any results"
End If