Looping through months and years

[eran3185]

Hi

I Want to build an excel file that looks like this:
On column A the user put years (i.e: a1 is 2015 , a2 is 2016 , a3 is 2017)
I want the macro will put on column C the year/month (i.e: c1 is 1/2015 , c2 is 2/2015 , c12 is 12/2015 , c13 is 1/2016 , c36 is 12/2017)
I want to get 36 cells with year/month.

I try to build this macro , but its not work ok.

This is what i have:



Sub loop()

Dim i As Integer, j As Integer, t As Integer


Columns("c:c").Select
Selection.NumberFormat = "@"


t = Application.WorksheetFunction.CountA(Columns(1)) * 12


For j = 1 To 2
For i = 1 To 12
For m = 1 To t

Range("c" & m).Value = i & "/" & Range("a" & j)

Next m
Next i
Next j

End Sub

I need help to do it correct
TNX

 

[Rick Rothstein]

I know you asked for a macro, but can you use a formula? If so, put this formula in cell C1 and copy it down to cell C36...

=MOD(ROWS(C$1:C1)-1,12)+1&"/"&INDEX(A$1:A$3,INT((ROWS(C$1:C1)-1)/12)+1)

If you could have a different number of cells in Column A, then change range I highlighted in red above to the range actually containing your list of years (make sure to retain the $ signs as I show them) and, of course, copy the formula down to the row whose number is 12 times the number of cells in that range.

 

[Rick Rothstein]

I know you asked for a macro, but can you use a formula? If so, put this formula in cell C1 and copy it down to cell C36...

But if you do need a macro, then give this one a try...

[table="width: 500"]
[tr]
	[td]Sub MonthsYears()
  Dim Rng As Range
  Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  With Rng.Cells(1).Offset(, 2).Resize(12 * Rng.Rows.Count)
    .Formula = "=MOD(ROWS(C$1:C1)-1,12)+1&""/""&INDEX(" & Rng.Address(1, 1) & ",INT((ROWS(C$1:C1)-1)/12)+1)"
    .NumberFormat = "@"
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]

 

[jorismoerings]

deleted

 

[eran3185]

very nice
tnx