How to cycle through dates incrementally by month in VBA

[Claire_Brummell]

Hi,

I'm trying to use VBA to create a 'calendar' style report.

Basically when you run the code to create the calendar it asks the user to enter a start and end date which it will then collate the information for. The information rather than being stored to a specific date, is linked to a month and year - so the date an item could be linked to would be Oct-2006 or Feb-2007 (though these are stored as full dates in the sheet - appearing as 01/12/2006 or 01/02/2007).

When I create the new sheet (which will house the calendar) I want it to create the calendar headings in the 3rd row starting at the second column, for the range of dates that the user has input.

So for example if the start date was 01/01/06 and the end date was 01/10/07, I want cell B3 to be "Jan-2006", C3 to be "Feb-2006", D3 to be "Mar-2006" right the way through to "Oct-2007". I'd like the values to be 01/01/06, 01/02/06 up to 01/10/07 and then change the format of the row to be "mmm-yyyy" so that I can use them for calculations if necessary.

Can anyone help?

 

[DominicB]

Hi

OK, that being the case, add this to the bottom of your code :

a = Month(Calendar_Start_Date)
b = 2
c = Year(Calendar_Start_Date)
d = Month(Calendar_End_Date)
mth = Month((Calendar_End_Date)) - Month(Calendar_Start_Date)
yr = Year(Calendar_End_Date) - Year(Calendar_Start_Date)
For n = 0 To ((12 - Month(Calendar_Start_Date) + 1) + ((yr - 1) * 12) + d)
Cells(3, b).Value = Format("01/" & a & "/" & c, "mmm-yyyy")
a = a + 1
b = b + 1
If a = 13 Then
c = c + 1
a = 1
End If
Next n

This now uses the results of your input boxes to determine the start and end dates.

HTH

DominicB

 

[Claire_Brummell]

Hi Dominic,

This is great, I'm almost there! But it's going just one month too far (ie, it's printing Nov-2006 if the end date given was Oct-2006) - I'm not sure exactly where to pull it back a month in the code you've given me...

 

[DominicB]

Hi Claire_Brummell

OK. Replace this line :

For n = 0 To ((12 - Month(Calendar_Start_Date) + 1) + ((yr - 1) * 12) + d)

With this :

For n = 0 To (12 - Month(Calendar_Start_Date) + ((yr - 1) * 12) + d)

Can't quite remember why I added the +1 into that line, it seemed to make sense at the time, but it seems to work alright now.

HTH

DominicB

 

[Claire_Brummell]

LOL - Thanks Dominic - much appreciated

 

[Claire_Brummell]

Hey

One more quick question - is there an easy way from this of deriving the number of months held on the sheet?

Thanks!

Claire

 

[DominicB]

Hi

Sure. The "For n=" line holds this info but it starts from zero so you need to ... add 1 (ching - penny's dropped, see above!!!) I originally started the For...Next loop from 1, then changed it to zero - that's why I added the 1. So a line such as this will flag up the number of months :

MsgBox (12 - Month(Calendar_Start_Date) + ((yr - 1) * 12) + d) + 1 & " months shown"

HTH

DominicB

 

[Claire_Brummell]

Brilliant! Thanks!