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

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


--
Regards,
Rocky McKinley

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

AFAIK, Find function cannot be used in UDF.
Why dont't you use match function instead

--
Message posted from http://www.ExcelForum.com

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

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

--
Regards,
Rocky McKinley


"Colo " wrote in message
...
AFAIK, Find function cannot be used in UDF.
Why dont't you use match function instead?


---
Message posted from http://www.ExcelForum.com/

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

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

--
Regards,
Rocky McKinley


"Colo " wrote in message
...
AFAIK, Find function cannot be used in UDF.
Why dont't you use match function instead?


---
Message posted from http://www.ExcelForum.com/

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

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

--------------------



---
Message posted from http://www.ExcelForum.com/

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

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.

--
Regards,
Tom Ogilvy

Colo wrote in message
...
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

--------------------



---
Message posted from http://www.ExcelForum.com/

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

Thanks Tom it works fine now.

--
Regards,
Rocky McKinley


"Tom Ogilvy" wrote in message
...
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 wrote in message
...
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

--
Regards,
Rocky McKinley


"Colo " wrote in message
...
AFAIK, Find function cannot be used in UDF.
Why dont't you use match function instead?


---
Message posted from http://www.ExcelForum.com/

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

Thanks Tom, :D

Tom Ogilvy wrote:
*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.

--
Regards,
Tom Ogilvy

Colo wrote in message
...
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

--------------------



---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/