For loop in VBA using date range

[davidtaylor598]

Hi all,

I have two dates taken in via the InputBox function and have called the sdate and edate.

I have coded it so that sdate is placed in cell A1.

I would like to use a loop to enter in dates down column A using the formula, =EDATE(A1,1) ie if we were looking at A2. A3 would be =EDATE(A2,1).

I would like the last entry to be edate.

Any help would be great,

thanks.

 

[Rick Rothstein]

I have two dates taken in via the InputBox function and have called the sdate and edate.

I have coded it so that sdate is placed in cell A1.

I would like to use a loop to enter in dates down column A using the formula, =EDATE(A1,1) ie if we were looking at A2. A3 would be =EDATE(A2,1).

I would like the last entry to be edate.

I am not sure I understand how that would work. Can you post an example of an sdate and an edate and show us the output (values, not formulas) that you want to see in A1 on downward? Obviously, try and keep the list somewhat short.

 

[davidtaylor598]

Sorry for not being clear.

As an example, if I use the InputBox to enter an sdate of 08/01/12 (August 1st 2012), and an edate of 12/01/12. Then I would have the following in cells A1 downwards:

08/01/2012
09/01/2012
10/01/2012
11/01/2012
12/01/2012

ending with the edate.

Does this help?

 

[AlphaFrog]

Do you want a date for each month between the two dates?

     MonthDiff = DateDiff("m", sdate, edate)
     
     Range("A1") = sdate
     
     [color=darkblue]With[/color] Range("A2").Resize(MonthDiff)
        .FormulaR1C1 = "=EDATE(R[-1]C,1)"
        .NumberFormat = Range("A1").NumberFormat
        [color=green]'.Value = .Value     'Convert formulas to values[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[davidtaylor598]

Do you want a date for each month between the two dates?

     MonthDiff = DateDiff("m", sdate, edate)
     
     Range("A1") = sdate
     
     [COLOR=darkblue]With[/COLOR] Range("A2").Resize(MonthDiff)
        .FormulaR1C1 = "=EDATE(R[-1]C,1)"
        .NumberFormat = Range("A1").NumberFormat
        [COLOR=green]'.Value = .Value     'Convert formulas to values[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

I've just realised a silly mistake. My 'edate' above should not have this name as this is the name of the function I am trying to use. I have changed it to 'enddate'.

Is there anyway you can copy down the 'EDATE' formula above till it reaches the enddate?

 

[Rick Rothstein]

I've just realised a silly mistake. My 'edate' above should not have this name as this is the name of the function I am trying to use. I have changed it to 'enddate'.

Is there anyway you can copy down the 'EDATE' formula above till it reaches the enddate?

Since you are doing your work in VBA, do you really need to insert formulas into the cells? Why not just insert the calculated dates into the cells directly? If you do that, you can leave the variable name as edate...

Range("A1").Value = SDate
Range("A1").DataSeries xlColumns, xlChronological, xlMonth, , edate

 

[AlphaFrog]

I've just realised a silly mistake. My 'edate' above should not have this name as this is the name of the function I am trying to use. I have changed it to 'enddate'.

Is there anyway you can copy down the 'EDATE' formula above till it reaches the enddate?

That's what the code does. Change edate to enddate if you like.

 

[davidtaylor598]

Thanks for the help guys!