How do I return the cell location (in addition to the value contained in the
cell) of the maximum value in an Excel array?

I'm trying to locate the row number for the maximum value in a column of
numbers.

Here is a function that returns a cell (range object) of the highes value in
a speicfied range.

Public Function MaxValue(ByVal rngToSearch As Range) As Range
Set MaxValue = rngToSearch.Find(Application.Max(rngToSearch))
End Function

Sub test()
Dim rng As Range

Set rng = MaxValue(ActiveSheet.Range("B:B"))
MsgBox rng.Value & vbTab & rng.Address
End Sub
--
HTH...

Jim Thomlinson


"Doug" wrote:

How do I return the cell location (in addition to the value contained in the
cell) of the maximum value in an Excel array?

I'm trying to locate the row number for the maximum value in a column of
numbers.

Doug wrote:
How do I return the cell location (in addition to the value contained in the
cell) of the maximum value in an Excel array?

I'm trying to locate the row number for the maximum value in a column of
numbers.

The following will return the location of the max within the column. If it
returns "1" it's the first element of the array, and so forth.

[ ] = MATCH(MAX(A1:A10),A1:A10)

Bill