VBA - dragging down date

[Fredek]

Hi guys,

I need a bit of help writing a code that will effectively drag the last date in column B up to yesterday's date.

The first date in cell B2 is 02/04/2012. I could either start at 02/04/2012 and drag it down by the number of days so something like 'yesterday's date minus 02/04/2012. Or find the last date in column B and drag it down to yesterday's date, again doing some subtraction to find the number of dates to add.

Any help would be very much appreciated.

Many thanks,
Fred

 

[JackDanIce]

In B2, Enter

=DATE(2012,4,ROW())

Then drag down until the desired date is reached

 

[FormR]

Or find the last date in column B and drag it down to yesterday's date, again doing some subtraction to find the number of dates to add.

One option for this might be..

With Range("B" & Rows.Count).End(xlUp)
    .AutoFill .Resize(Date - .Value), xlFillSeries
End With

 

[Fredek]

Thank you very much! Both the VBA and formula solutions work great!

 

[Fredek]

FormR, when I run the code twice in a day I get a Run Time Error '1004'.

Is there an efficient way to get past this? Basically so that nothing happens if the yesterday's date has already been added to the spreadsheet?

 

[FormR]

Hi, you could try like this:

With Range("B" & Rows.Count).End(xlUp)
    If .Value < Date - 1 Then .AutoFill .Resize(Date - .Value), xlFillSeries
End With

 

[Fredek]

Amazing, thank you!