Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture date for =MAX(a1:a250) formula
My spreadsheet has a date column and an amount column. What I want to do is
get the date for the maximum in the amountcolumn. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture date for =MAX(a1:a250) formula
With the dates in column A and the amounts in column B, you can use a formula
like: =index(a:a,match(max(b:b),b:b,0)) In code: dim res as variant dim myRng as range with activesheet set myrng = .range("b:b") end with with application if .count(myrng) = 0 then msgbox "No Numbers!" else res = .match(.max(myrng),myrng,0) end if end with if iserror(res) then 'this shouldn't ever happen! else msgbox myrng.cells(1,1).offset(res-1,-1).value end if Rick I wrote: My spreadsheet has a date column and an amount column. What I want to do is get the date for the maximum in the amountcolumn. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture date for =MAX(a1:a250) formula
Just a rearrangement of code to stop some potential errors:
dim res as variant dim myRng as range with activesheet set myrng = .range("b:b") end with with application if .count(myrng) = 0 then msgbox "No Numbers!" else res = .match(.max(myrng),myrng,0) if iserror(res) then 'this shouldn't ever happen! else msgbox myrng.cells(1,1).offset(res-1,-1).value end if end if end with Dave Peterson wrote: With the dates in column A and the amounts in column B, you can use a formula like: =index(a:a,match(max(b:b),b:b,0)) In code: dim res as variant dim myRng as range with activesheet set myrng = .range("b:b") end with with application if .count(myrng) = 0 then msgbox "No Numbers!" else res = .match(.max(myrng),myrng,0) end if end with if iserror(res) then 'this shouldn't ever happen! else msgbox myrng.cells(1,1).offset(res-1,-1).value end if Rick I wrote: My spreadsheet has a date column and an amount column. What I want to do is get the date for the maximum in the amountcolumn. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I capture a date? | Excel Discussion (Misc queries) | |||
Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
want to capture date | Excel Discussion (Misc queries) | |||
Formula to capture data on a date and preserve it | Excel Worksheet Functions | |||
How do I capture the Modified date in a cell? | Excel Programming |