address of min or max result
 

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

--


Gary

address of min or max result
 

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

--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

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

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

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


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

you may like this better

maxaddress = Columns(1).find(Application.Max(Columns(1))).Addre ss

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
--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

hi don:

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

--


Gary


"Don Guillett" wrote in message
...
you may like this better

maxaddress = Columns(1).find(Application.Max(Columns(1))).Addre ss

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
--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

In the case of looking in ONE row, try this

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

--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hi don:

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

--


Gary


"Don Guillett" wrote in message
...
you may like this better

maxaddress = Columns(1).find(Application.Max(Columns(1))).Addre ss

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
--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary

address of min or max result
 

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.

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
In the case of looking in ONE row, try this

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

--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hi don:

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

--


Gary


"Don Guillett" wrote in message
...
you may like this better

maxaddress = Columns(1).find(Application.Max(Columns(1))).Addre ss

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
--
Don Guillett
SalesAid Software

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
thanks, both of you, ended up getting this from your help.

application.worksheetfunction.match(application.wo rksheetfunction.max(range("c23:k23")),range("c23:k 23"),0)

--


Gary


"Trevor Shuttleworth" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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

--


Gary