CODEWITHSUNDEEP
vbaexcelobject
skiaddict1
skiaddict1

Reputation: 525

VBA: Returning Range.Find() result from a function

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

Answers (1)

R3uK
R3uK

Reputation: 14557

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

Related Questions