Which Function does....

[comish4lif]

I have a range of cells (about 15 rows (people) and 180
columns(dates)).

I am using max to identify the highest value within that range. Now
that I have that many cells, which function would I use to identify the
row (row 1) and column header (colmn A) labels associted with that max
value?

 

[Bill Kuunders]

did this macro for a different problem a few days back.
It should work for you though


If your max function is in A18
the result will be in B18 and C18


Sub testmax()
Dim myrange As Range
Set myrange = Range("A1:FY15")
Range("B18").Activate
For Each cell In myrange
If cell.Value = Range("A18").Value Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub

 

[Harlan Grove]

(e-mail address removed) wrote...

I have a range of cells (about 15 rows (people) and 180
columns(dates)).

I am using max to identify the highest value within that range. Now
that I have that many cells, which function would I use to identify the
row (row 1) and column header (colmn A) labels associted with that max
value?

You could do this with formulas. If your values were in a range named
TBL, you could use either

=OFFSET(Tbl,MATCH(TRUE,COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),
A18)>0,0)-1,-1,1,1)&" "&OFFSET(Tbl,-1,MATCH(A18,INDEX(Tbl,MATCH(TRUE,
COUNTIF(OFFSET(Tbl,ROW(Tbl)-CELL("Row",Tbl),0,1,),A18)>0,0),0),0)-1,1,1)

or

=OFFSET(Tbl,-1,MATCH(TRUE,COUNTIF(OFFSET(Tbl,0,COLUMN(Tbl)
-CELL("Col",Tbl),,1),A18)>0,0)-1,1,1)&" "&OFFSET(Tbl,MATCH(A18,
INDEX(Tbl,0,MATCH(TRUE,COUNTIF(OFFSET(Tbl,0,COLUMN(Tbl)
-CELL("Col",Tbl),,1),A18)>0,0)),0)-1,-1,1,1)

The first matches the row then the column, the latter the column then
the row. You need to decide which you want in case there are multiple
instances of the max value, e.g.,

A B C D
X 1 2 9 4
Y 3 5 6 7
Z 9 8 0 1

With the {1,2,9,4;3,5,6,7;9,8,0,1} named Tbl, the first formula above
would return "X C" while the second would return "A Z".

 

[Ron Rosenfeld]

I have a range of cells (about 15 rows (people) and 180
columns(dates)).

I am using max to identify the highest value within that range. Now
that I have that many cells, which function would I use to identify the
row (row 1) and column header (colmn A) labels associted with that max
value?

You could use this UDF:

=============
Function MaxAddress()
Dim c As Range
Dim MaxValue

MaxValue = Application.WorksheetFunction.Max(Range("tbl"))

Set c = Range("tbl").Find(MaxValue, LookIn:=xlValues)
MaxAddress = c.Address

End Function
====================


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

You could use this UDF:

=============
Function MaxAddress()
Dim c As Range
Dim MaxValue

MaxValue = Application.WorksheetFunction.Max(Range("tbl"))

Set c = Range("tbl").Find(MaxValue, LookIn:=xlValues)
MaxAddress = c.Address

End Function
====================

Why not parametrize the range?


Function foo(rng As Range) As String
Dim c As Range

On Error Resume Next

Set c = rng.Find(What:=Application.WorksheetFunction.Max(rng))

If c Is Nothing Then
foo = "" 'no numbers in rng
ElseIf Application.Caller.Parent Is rng.Parent Then
foo = c.Address(0, 0, xlA1, 0)
Else
foo = c.Address(0, 0, xlA1, 1)
End If

End Function

 

[Dave Peterson]

..Find in UDFs called from worksheet cells will work with xl2002+, but not in
xl2k (or earlier), IIRC.

 

[Harlan Grove]

Dave Peterson wrote...

.Find in UDFs called from worksheet cells will work with xl2002+, but not in
xl2k (or earlier), IIRC.

....

I believe you're right. In which case the backward compatible approach
would be


Function foo(rng As Range) As String
Dim v as Variant, c As Range

v = Application.WorksheetFunction.Max(rng)

If Application.WorksheetFunction.Count(rng) = 0 Then
foo = "" 'no numbers in rng

Else
For Each c In rng
If c.Value = v Then Exit For
Next c

If Application.Caller.Parent Is rng.Parent Then
foo = c.Address(0, 0, xlA1, 0)
Else
foo = c.Address(0, 0, xlA1, 1)
End If

End If

End Function

 

[Ron Rosenfeld]

Why not parametrize the range?

Probably simpler your way.


--ron