Repeat a For Loop in vba

Question

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.

QHarr · Accepted Answer · 2021-03-12 05:44:32Z

1

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

edited Mar 12, 2021 at 5:44

answered Mar 12, 2021 at 1:55

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KomVVi Over a year ago

Thank you, what does i = 1 To 14 * (last_cell - 1) Step last_cell - 1 , means ?

QHarr Over a year ago

last_cell IIRC was calculated as 5 so i = 1 To 14 * (5-1) Step 5-1 which is the same as i = 1 To 56 Step 4 , means i will go 1, 5, 9 etc... increasing by 4 every time through loop. Your inner loop then goes round from y = 2 to y = 5 i.e. 3 times so each time through both loops you will have gone 1,2,3,4 next time 5,6,7,8 etc.......

Collectives™ on Stack Overflow

Repeat a For Loop in vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related