CODEWITHSUNDEEP
excelvba
KomVVi
KomVVi

Reputation: 17

Repeat a For Loop in vba

My code gives me a series of years from 2016 to 2019

Max = WorksheetFunction.Max(Workbooks("x.xltm").Worksheets("y").Range("A:A"))
nb_year = Max - 2016
last_cell = 2 + nb_year
    new_y = 0
        For Y = 2 To last_cell
            Workbooks("x.xltm").Worksheets("z").Cells(Y, 2) = 2016 + new_y
            new_y = new_y + 1
        Next Y

But I want to repeat it 14 times, thus I would have , [2016,2017,2018,2019,2016..], I guess I have to use a Do While or While but I don't really know the condition I should put. Thank you.

Upvotes: 1

Views: 116

Answers (1)

QHarr
QHarr

Reputation: 84465

You want an outer loop and a little maths

Option Explicit  
Public Sub test()
    Dim max_value As Long, nb_year As Long
    Dim y As Long, last_cell As Long

    max_value = WorksheetFunction.Max(Workbooks("x.xltm").Worksheets("y").Range("A:A"))
    nb_year = max_value - 2016
    last_cell = 2 + nb_year
    
    Dim i As Long
    
    For i = 1 To 14 * (last_cell - 1) Step last_cell - 1
        For y = 2 To last_cell
            ThisWorkbook.Worksheets("z").Cells(y + i - 1, 2) = 2014 + y
        Next y
    Next
 
End Sub

Upvotes: 1

Related Questions