Ron,

I am using your code for returning the address of the maximum value in the
range and it works great, thanks.

I was trying to get the second bit of code to work to return the address of
all the maximum values in a range but I'm having a bit of difficulty figuring
out how it works.

I've replaced the UDF with the second bit of code, but the cell still only
returns one address. What should happen when there are two or more maximum
values in the range?

The cell formula I have is: {=maxadr(D2:W15)}

Many thanks
Scott.

"Ron Rosenfeld" wrote:

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug"
wrote:

I'd like to have the cell address returned along with a value when I use the
MAX function. Is there a way to do that?

Due to Harlan's critique, I found some other issues with my recommendation.

So, being lazy, I would just use a VBA routine to accomplish the task, if you
need it for a 2D reference.

To enter this UDF, <alt<F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module (from the main menu bar on top) and paste the code below into the
window that opens.

To use the function, enter =MaxAdr(rng) in some cell where "rng" is the range
you wish to search.

As written, it will return the address of the first MAX number it encounters.
If you want multiple addresses returned, that would be a simple modification,
depending on how you wanted the addresses returned (comma separated in the same
cell, or as an array).

=========================
Function MaxAdr(rng As Range) As String
Dim c As Range
Dim MaxNum As Double

MaxNum = Application.WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = MaxNum Then
MaxAdr = c.Address
Exit Function
End If
Next c
End Function
=======================

For example, the following will return an array with ALL of the addresses
containing the MAX number in the range:

=========================
Function MaxAdr(rng As Range)
Dim c As Range
Dim MaxNum As Double
Dim Temp()
Dim d As Long

MaxNum = Application.WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = MaxNum Then
ReDim Preserve Temp(d)
Temp(d) = c.Address
d = d + 1
End If
Next c
MaxAdr = Temp
End Function
========================


--ron