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.

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

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