hpchong7
asked on
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!
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!
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, you have this link.