Gary''s Student wrote...
....
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
....

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.

This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Harlan Grove" wrote in message ups.com...
Gary''s Student wrote...
....
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
....

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.

Depends on the version of excel.

..find won't work with xl97.

..find works with xl2002+

I've blacked out what happens with xl2k (stupid brain!).

Jim Cone wrote:

This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Harlan Grove" wrote in message ups.com...
Gary''s Student wrote...
...
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
...

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.

--

Dave Peterson

Dave,
Thanks for that.
I just tried it in XL2000 and it does not work there.
However, that will not be a problem after the entire world upgrades. <g
--
Jim Cone


"Dave Peterson"
wrote in message
Depends on the version of excel.
..find won't work with xl97.
..find works with xl2002+
I've blacked out what happens with xl2k (stupid brain!).

Jim Cone wrote:
This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


--

Dave Peterson

Thanks for posting back. That's the way I (kind of) remember it.

Jim Cone wrote:

Dave,
Thanks for that.
I just tried it in XL2000 and it does not work there.
However, that will not be a problem after the entire world upgrades. <g
--
Jim Cone

"Dave Peterson"
wrote in message
Depends on the version of excel.
.find won't work with xl97.
.find works with xl2002+
I've blacked out what happens with xl2k (stupid brain!).

Jim Cone wrote:
This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

--

Dave Peterson

--

Dave Peterson

Thank you Harlan & Jim (once again!)

I don't know if superfoo2 is perfect, but it is a heck of a lot better than
what I originally posted.
--
Gary's Student


"Jim Cone" wrote:

This seems to work...
Function superfoo2(r As Range) As String
Dim m As Double
m = Application.Max(r)
superfoo2 = r.Find(What:=m, after:=r(r.Count)).Address
End Function
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Harlan Grove" wrote in message ups.com...
Gary''s Student wrote...
....
Function superfoo(r As Range) As String
Dim s As String
m = Application.Max(r)
superfoo = r.Find(What:=m, After:=ActiveCell).Address
End Function

This throws a #VALUE! error.

What am I doing wrong?
....

Find is a *method* rather than a property of the Range class. You can't
run methods from udfs because udfs can't do anything other than return
values to the calling cell. The Find method tries to change ActiveCell,
but udfs can't do that.