Automatically increase the month of a date and put it in new cells, based on a cell value?

[ianwuk]

Hello.

I have this spreadsheet:

How can I make it so that, for whatever number I put in B1 it takes the date in A4 and automatically fills in rows below by increasing the months.

So, for example, if I put 3 in B1 I want it to automatically look like this:

How can I do this? Is it a formula or macro?

Many thanks.

 

[AlanY]

try this in A5 and copy down

=IF((ROW(A5)-ROW($A$4))<=$B$1,EDATE(A4,1),"")

 

[ianwuk]

try this in A5 and copy down

=IF((ROW(A5)-ROW($A$4))<=$B$1,EDATE(A4,1),"")

Thanks so much for replying. I will try it tomorrow. Is there any way for it to add the extra rows automatically, without having to copy down?

 

[dfsobral]

if you don't want to copy down, you can even set an event when you change cell B1

try this

Sub FillMonths()
     Range("A4")=Range("B2")
     If Range(""B1")>1 Then
          Range(Range("B5"), Range("B5").Offset(Range("B2")-2, 0).FormulaR1C1= "=EDATE(R[-1]C,1)"
          Range(Range("B4"), Range("B4").Offset(Range("B2")-1, 0).NumberFormat="dd-mm-yy"
     End If
End Sub

 

[ianwuk]

if you don't want to copy down, you can even set an event when you change cell B1

try this

Sub FillMonths()
     Range("A4")=Range("B2")
     If Range(""B1")>1 Then
          Range(Range("B5"), Range("B5").Offset(Range("B2")-2, 0).FormulaR1C1= "=EDATE(R[-1]C,1)"
          Range(Range("B4"), Range("B4").Offset(Range("B2")-1, 0).NumberFormat="dd-mm-yy"
     End If
End Sub

Silly question, but this would be as a new Macro, is that correct? Thanks.

 

[dfsobral]

yes

you would create this macro and run it when you want to fill all the months

if you want to an automatic run of the macro when you insert the number of months you could adapt it to a change event