Finding a range from a column using VBA.

M

Mike Echo

I have a table of info with set data in columns and dates down the first
column (A). What is the easiest way to get a range of dates (eg one
month)? Getting the related data via offset I understand, but I don't
know how to find the startrow and endrow to begin.

Any help appreciated, even a general pointer for me to research. I just
don't know where to start.

TIA.

R.
 
A

Arvi Laanemets

Hi

=MATCH(DATE(2005,4,1),A:A,1)
returns the row number of cell with first April date in column A (i.e. when
the first cell in column A with April 2005 date contains April 2nd 2005,
then this row is returned. As follows, you must have column A sorted to work
properly). Or the position of first May, June, ... etc date, when there is
no date from April 2005.
=MATCH(DATE(2005,4,1),A:A,0)
returns the row number of first occurrence of April 1st 2005 in column A.
When there is not such a date, an error is returned.

You can both expressions use in OFFSET to calculate the second parameter
(the number of rows to offset from base cell)
 
B

Biff

Hi!

Not sure how to implement this in VBA but a worksheet formula might start
you in the right direction.

Using Offset to find the start and end of the range:

I assume the dates are in consecutive order ascending and you want data for
the entire month.

=OFFSET(A1,MATCH(1,MONTH(A1:A100),0)-1,,EOMONTH(1,0))

I'm using the month of January in my example.

You use the Match function to find the start of the range. January has 31
days in it so you want a range 31 rows from the start of the range.

Eomonth (requires the Analysis ToolPak add-in) is the HEIGHT argument and
evaluates to 31.

Now, if I were entering this as a worksheet formula I would select a range
of cells 31 rows high and enter the formula as an array.

Biff
 
M

Mike Echo

Not sure how to implement this in VBA but a worksheet formula might start
you in the right direction.
[...]
I have a table of info with set data in columns and dates down the first
column (A). What is the easiest way to get a range of dates (eg one
month)? Getting the related data via offset I understand, but I don't
know how to find the startrow and endrow to begin.

Any help appreciated, even a general pointer for me to research. I just
don't know where to start.
Click to expand...
Click to expand...

Thanks very much Biff and Arvi, I am on the right track now.

R.
 
Top