current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
I very much appreciate the details explanation on the issue. I try the last one and it works. I did use "SET" on mine, I think I should have need to handle the error in case no value found. –  NCC Apr 18 '13 at 21:00
    
Glad to be of help :) –  Siddharth Rout Apr 18 '13 at 21:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.