Passing a Range to a Function Problem
 

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

Passing a Range to a Function Problem
 

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

Passing a Range to a Function Problem
 

Thanks Chip, I appreciate the help.

--
Regards,
Rocky McKinley


"Chip Pearson" wrote in message
...
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

Passing a Range to a Function Problem
 

Hi Chip there is still a problem, FindIt should return "" or an address.

Function FindIt(Ranger As Range) As String
Dim C As Range
FindIt = ""
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
X = FindIt Range("a1:a4")
MsgBox X
End Sub

--
Regards,
Rocky McKinley


"Chip Pearson" wrote in message
...
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

Passing a Range to a Function Problem
 

since you are using the returned value of the function, you should enclose
the argument in brackets. this worked fine for me:

Function FindIt(Ranger As Range) As String
Dim C As Range
FindIt = ""
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
Dim x As String
x = FindIt(Range("a1:a4"))
MsgBox x
End Sub


--
Regards,
Tom Ogilvy


Rocky McKinley wrote in message
...
Hi Chip there is still a problem, FindIt should return "" or an address.

Function FindIt(Ranger As Range) As String
Dim C As Range
FindIt = ""
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
X = FindIt Range("a1:a4")
MsgBox X
End Sub

--
Regards,
Rocky McKinley


"Chip Pearson" wrote in message
...
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