Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining the Cell Address of a VLOOKUP Result in VBA | Excel Discussion (Misc queries) | |||
How can I use the result from ADDRESS in another formula | Excel Worksheet Functions | |||
Using address function result in an array | Excel Worksheet Functions | |||
Using Address Result | Excel Discussion (Misc queries) | |||
Using result from ADDRESS function as a cell reference itself | Excel Worksheet Functions |