address of min or max result

[Gary Keramidas]

is there an easy way to return the address of the min or max result? if it's a
lot of code, i'm not going to worry about it, it's not that important. but if
there's an easy way, i'd like to know.

thanks

 

[Don Guillett]

one way in a known column
="a"&MATCH(MAX(A:A),A:A,0)

 

[Trevor Shuttleworth]

Gary

=MATCH(MAX(A:A),A:A,0)

will return the row number of the maximum value in column A

=MATCH(MIN(A:A),A:A,0) for the minimum

Regards

Trevor

 

[Gary Keramidas]

thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.worksheetfunction.max(range("c23:k23")),range("c23:k23"),0)

 

[Trevor Shuttleworth]

Thanks for the feedback.

The code gives the position within the data range. If you need the
"address" or the column you'll need to adjust the result.

Any likelihood that you'll have duplicate maximum or minimum values ? The
match will only return the first occurrence.

Regards

Trevor

 

[Don Guillett]

you may like this better

maxaddress = Columns(1).find(Application.Max(Columns(1))).Address

change to your range if desired
Sub findmax()
Set myrange = Range("c23:k23")'or rows(23)
mc = myrange.find(Application.Max(myrange)).Address
MsgBox mc
End Sub

 

[Gary Keramidas]

hi don:

i get an object variable or with block variable not set error when i run it.

 

[Don Guillett]

In the case of looking in ONE row, try this

ma = Rows(23).find(Application.Max(Rows(23))).Address
MsgBox ma

 

[Tom Ogilvy]

Find seems sensitive to format when working with decimal values. Your
problem is that find isn't finding a match and raising the error. Probably
better to use Match as previously suggested.