excel VBA return range object from a function

Dear all,

   I 've a function which will return a range object:

Dim temp as range
set temp = findit("abc")
....

end sub

Function Findit(Expr As String)
Dim col_head As Range
Range("A1").Activate
Set col_head = Cells.Find(What:=Expr, LookAt _
       :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False)
Findit= col_head
End Function

However , the problem is that after the function is excuted, there is error when returning to the line set temp = findit("abc").do anyone know what's the problem?thanks!
LVL 2
hpchong7Asked:
Who is Participating?
 
blakeh1Commented:
I had problems running, it would actually error out on the
line
Findit= col_head
with an "Object Variable not set" error

I changed that line to
Set Findit = col_head
and I have no problem runnning it.
(Note: the variable will be "Nothing" if no match was found)

Sub testFindit()
    Dim temp As Range
    Set temp = Findit("ax")
End Sub
Function Findit(Expr As String) As Range
    Dim col_head As Range
    Range("A1").Activate
    Set col_head = Cells.Find(What:=Expr, LookAt _
          :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False)
    Set Findit = col_head
End Function
 
0
 
criCommented:
0
 
nfernandCommented:
Hi guys... another tips:

Specify the data type returned: Function Findit(Expr As String) as Range, this way VBA has not to deal with variants that are slower to handle.

If all of this is used as a function inside a cell, no error menssages may be generated. All you see the most is an #error# or #N/A# value in the cell.

As I know (maybe I'm wrong) the find function generates an error if data cannot be found. SO you have to prevent this by trappnig the error with "on error".

Bye.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.