Rocky,
The problem is that you are enclosing the argument in parentheses
when you call the FindIt function. When you do this, VBA
evaluates that argument, and since the Value property is the
default property of a Range, VBA attempts to pass the value of
the range, not the actual range itself. You should enclose
arguments in parentheses only when the function call is returning
a value, or you want to force a ByRef argument to ByVal. Get rid
of the parentheses in the function call and you should be all
set. E.g.,
FindIt Range("a1:a4")
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Rocky McKinley" wrote in message
...
Hi when I run the TestIt sub below I get an error "Object
Required". What
am I doing wrong?
Function FindIt(Ranger As Range) As String
Dim C As Range
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function
Sub TestIt()
FindIt (Range("a1:a4"))
End Sub
--
Regards,
Rocky McKinley