Need VB code for excel to pupulate specific date ranges

[benny1983]

Hi fantastic members,

I have created a monstrous calculator for work within my organisation. I have a user inoput screen to get a start and end date so I am pulling variables from there.

What i need is to be able to populate cells starting at A12:B12 with the start date in cell A12, then adding one month and getting that to populate into cell B12.

I need this to continue subsequently until the end date is reached. Managed to find this code which starts and stops on the dates I want but it populates days and only in column A.

Any help would be much appreciated. Just to clarify I would be looking at something like this:

A12 shows "06/10/10"
B12 shows "05/11/10"
A13 shows "06/11/10"
B13 shows "05/12/10" etc etc until the end date is reached

Private Sub CommandButton21_Click()
 
Dim startdate As Date
Dim enddate As Date
Dim row As Double
 
startdate = Range("D9").Value
enddate = Range("D10").Value
row = 0
 
With Worksheets("Sheet1")
Range("A12:B12").Select
Do Until DateAdd("d", 1, startdate) = enddate + 1
    ActiveCell.Offset(row, 0).Value = DateAdd("d", 1, startdate)
    startdate = startdate + 1
    row = row + 1
Loop
End With
End Sub
 

[twinnyfo]

benny1983,

Could you explain a little bit more about exactly what it is you want? Are you adding one month or adding 30 days? There is a huge difference. If you are adding one month, then change the value in the DateAdd() function. Otherwise, use 30 as the argument. Here is a sample:

Private Sub CommandButton21_Click()
    Dim startdate As Date
    Dim enddate As Date
    Dim row As Double
 
    startdate = Range("D9").Value
    enddate = Range("D10").Value
    row = 12
 
    With Worksheets("Sheet1")
        Do While Not startdate > enddate
            .Cells(row, 1) = startdate
            .Cells(row, 2) = DateAdd("m", 1, startdate) '1 month
            .Cells(row, 2) = DateAdd("d", 30, startdate) '30 days
            startdate = startdate + 1
            row = row + 1
        Loop
    End With
End Sub

Hope this hepps!