Passing a Range to a Function problem (still not working)

  • Thread starter Thread starter Rocky McKinley
  • Start date Start date
R

Rocky McKinley

Hi I still haven't found the solution to passing a range. Chip suggested
earlier to remove the brackets around the Range, I tried that and it still
doesn't work. Does anyone know what is wrong?

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
 
Actually it can be used if I pass the range address, but I haven't found a
way to pass the range directly.
The code below works.

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

Sub TestIt2()
MsgBox FindIt2("a1:a4")
End Sub
 
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

as did this

Sub TestIt2()
MsgBox FindIt(Range("a1:a4"))
End Sub

--
Regards,
Tom Ogilvy


Rocky McKinley said:
Actually it can be used if I pass the range address, but I haven't found a
way to pass the range directly.
The code below works.

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

Sub TestIt2()
MsgBox FindIt2("a1:a4")
End Sub
Click to expand...
 
Hi Rocky,

Sorry my prev post was not correct.
Your UDF works as follows.


Code:
--------------------

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

--------------------
 
Colo,

Generally (meaning in my experience) people use the term UDF (User defined
function) to mean the function will be called from a spreadsheet like a
built in Excel function

=MyUDF(A1:A4)

In that case, in xl2000 and earlier, Find does not work. Dave Peterson
reports it works in xl2002 when used in a UDF called from a worksheet.

If the Function is just called from a normal VBA Sub or VBA function, then
Find works.
 
Thanks Tom it works fine now.

--
Regards,
Rocky McKinley


Tom Ogilvy said:
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

as did this

Sub TestIt2()
MsgBox FindIt(Range("a1:a4"))
End Sub
Click to expand...
 
Thanks Tom, :D

Tom said:
*Colo,

Generally (meaning in my experience) people use the term UDF (User
defined
function) to mean the function will be called from a spreadsheet
like a
built in Excel function

=MyUDF(A1:A4)

In that case, in xl2000 and earlier, Find does not work. Dave
Peterson
reports it works in xl2002 when used in a UDF called from a
worksheet.

If the Function is just called from a normal VBA Sub or VBA function,
then
Find works.
Click to expand...
 
Back
Top