Hi Harlan:

Your suggestion to use MAX and then loop for it is very good. I tried the
following:

Sub findmax()
Dim s As String
m = Application.Max(Selection)
s = Selection.Find(What:=m, After:=ActiveCell).Address
MsgBox (s)
End Sub

That is using Find in place of a loop. This works. I then tried to make a
function:

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?
--
Gary''s Student


"Harlan Grove" wrote:

Gary''s Student wrote...
Sub findmax()

If there were OBVIOUSLY something for which a FUNCTION would be most
appropriate, this is it!

Dim r As Range
Set r = Selection
v = r.Cells(1, 1).Value
For Each r In Selection
If r.Value v Then
v = r.Value
s = r.Address
End If
Next
MsgBox ("maximum value " & v & " found in cell " & s)
End Sub

This can easily be converted into a function as well.

Should NEVER have been offerred as a sub. Parametrizing the function
isn't completely trivial, and deciding what to return (range object
pointing to the cell containing the max value, a string containing the
cell's address or an array of row and column indices) is worthy of some
thought.

Also inefficient. No reason to search through the entire range.
WorksheetFunction.Max will return the max value more quickly than
iterating through the range in VBA, then exit when the first instance
of the max value is found. The following returns the address of that
cell.

Function foo(rng As Range) As String
Dim c As Range, x As Double
x = Application.WorksheetFunction.Max(rng)
For Each c In rng
If c = x Then
foo = c.Address(0, 0)
Exit Function
End If
Next c
End Function

There's also the question of which direction to search. That is, if the
range contained

1 1 1 9 1
2 1 3 1 1
3 1 2 2 3
9 3 1 1 2
2 1 1 3 2

should the 9 in the top row or the 9 in the leftmost column be
considered the first max value found?

Finally, no need for udfs. This can be done with an array formula. If
the range were named D,

=ADDRESS(INT(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D) ))/1000),
MOD(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D))),1000))

would return the address of topmost then leftmost max value and

=ADDRESS(MOD(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D) )),1000),
INT(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D)))/1000))

would return the address of the leftmost then topmost max value.

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.