Fill dates across

[j_Southern]

I have a formula on one sheet which finds the date of the first Monday in April of a chosen year.
I want to copy this date to a second sheet and then fill across for all the days of that year (April to April).
I have not been able to solve this problem and would welcome some help.
I want, eventually, to automate this process in VBA
John

[CK76]

Copy paste as value. Then copy across. It will auto increment by 1 day per cell.

If you mean that you want to create array. You can do something like...

Please Login or Register  to view this content.

Note: I took across to mean dates in columns. If you mean to rows, then just use Application.Transpose(ar).

[Rick Rothstein]

Put your formula on the second sheet in the cell you want to start from, let's say cell B1 for example purposes, then put this formula in the next cell to the right (cell C1 for this example)...

=B1+1

and copy that cell to the right 363 columns (that would be cell NB1 for this example). Finally, put this formula in the next cell over (cell NC1 for this example) in order to handle Leap Years correctly...

=IF(MONTH(DATE(YEAR(NB1),2,29))<>2,"",NB1+1)

[Rick Rothstein]

If you wanted a macro solution like CK76 has posted (instead of the formula solution I gave you above), you can consider this one as well...

Please Login or Register  to view this content.

NOTE: The above code assumes the start date is taken from cell A1 on Sheet 1... change as required or, if desired, hard-code the date directly.