Hi All,

I have spreadsheet, in which col A has dates ranging from 11-1-2005 to
12-12-2009 and the report which i run is used every monday.

Now i want to determine the last row in column A which has the date
less than or equal to the date on which i am running my report.

ex.

if i run my report on 03-12-2007, then i want to determine the last
row address in col A which has the date equal to or less than
03-12-2007. Here, date 03-12-2007 is the upper limit for the data.

Hope the problem is clear. Please help.

thanks,
navin

If the dates are in Microsoft Date/Time format (not an ascii string) the
simply use VLOOKUP. the 3rd parameter range_lookup should be FALSE. The
dates in the tabler need to be in increasing order. Sort the table to get
the dates increasing if they aren't already.

"navin" wrote:

Hi All,

I have spreadsheet, in which col A has dates ranging from 11-1-2005 to
12-12-2009 and the report which i run is used every monday.

Now i want to determine the last row in column A which has the date
less than or equal to the date on which i am running my report.

ex.

if i run my report on 03-12-2007, then i want to determine the last
row address in col A which has the date equal to or less than
03-12-2007. Here, date 03-12-2007 is the upper limit for the data.

Hope the problem is clear. Please help.

thanks,
navin

For Vlookup as you stated, It would be the 4th parameter. the 3rd parameter
specifies what column in the lookup range to return. Also, it should be
True, not false. False says to find an exact match only.

all that said, Match is a better choice in this instance as JMay shows for a
worksheet formula.

In code

Dim res as Variant, dt as Date
Dt = Date
res = Application.Match(clng(dt),Range("A1:A500"),1)
set rng = Range("A1:A500")(res)

the column would need to be sorted ascending, but the request doesn't make
much sense if it isn't.

--
Regards,
Tom Ogilvy




"Joel" wrote:

If the dates are in Microsoft Date/Time format (not an ascii string) the
simply use VLOOKUP. the 3rd parameter range_lookup should be FALSE. The
dates in the tabler need to be in increasing order. Sort the table to get
the dates increasing if they aren't already.

"navin" wrote:

Hi All,

I have spreadsheet, in which col A has dates ranging from 11-1-2005 to
12-12-2009 and the report which i run is used every monday.

Now i want to determine the last row in column A which has the date
less than or equal to the date on which i am running my report.

ex.

if i run my report on 03-12-2007, then i want to determine the last
row address in col A which has the date equal to or less than
03-12-2007. Here, date 03-12-2007 is the upper limit for the data.

Hope the problem is clear. Please help.

thanks,
navin

Here's a sample - Try and apply to your situation
With dates in Col A, Say in range A1:A25

In C2 enter 12/15/2006 << your report date
In D2 enter =ADDRESS(MATCH(C2,A1:A25,1),1)

HTH

"navin" wrote in message
ups.com:

Hi All,

I have spreadsheet, in which col A has dates ranging from 11-1-2005 to
12-12-2009 and the report which i run is used every monday.

Now i want to determine the last row in column A which has the date
less than or equal to the date on which i am running my report.

ex.

if i run my report on 03-12-2007, then i want to determine the last
row address in col A which has the date equal to or less than
03-12-2007. Here, date 03-12-2007 is the upper limit for the data.

Hope the problem is clear. Please help.

thanks,
navin