Reputation: 525
I need to encapsulate Range.Find() in a function, so I have the following (R1, C1 etc are all defined consts):
Function FindData(FindWhat As Variant) As Range
Dim TheR As Range
Set TheR = Range(Cells(R1, C1), Cells(R2, C2)).Find(FindWhat, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
FindData = TheR
End Function
I'm passing in a string, so the call is e.g.:
Set FirstCell = Call FindData("MyValue")
VBA pukes on the line FindData = TheR
with the message Object variable or With block variable not set
. I've tried adding this if-block:
If (TheR Is Nothing) Then
FindData = Nothing
Else
FindData = TheR
End If
but it doesn't make any difference. How can I return the value that Find() gives me?
Upvotes: 3
Views: 3384
Reputation: 14547
For object variables, you need to add the Set
keyword, even in functions! ;)
So just add that at the end :
Function FindData(FindWhat As Variant) As Range
Dim TheR As Range
Set TheR = Range(Cells(R1, C1), Cells(R2, C2)).Find(FindWhat, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Set FindData = TheR
End Function
And when you use the Call
you won't get an output from the function, so you need to use parenthesis :
Set FirstCell = FindData("MyValue")
Upvotes: 3