Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
Passing a Range to a Function Problem | Excel Programming | |||
passing a range to a user defined function using a form | Excel Programming | |||
Passing an unkown range to a function | Excel Programming |