Why doesn't this for loop process the full data set?

Question

Background

I have a spreadsheet of ticket allocations for an event. On each row of the spreadsheet is a name and the number of tickets allocated.

I need to change the spreadsheet so that each name is duplicated once per ticket on separate rows, like this:

I have a macro to do this, however it exhibits strange behaviour

The Problem

The macro doesn't loop through the entire data set. Stepping through the code shows that, despite deliberately increasing the value of LastRow, the For loop only loops for however many times the original value specified. The new value of LastRow at the end of each iteration appears to be disregarded.

This seems particularly odd as the equivalent Do While loop works fine (See below for the working code using a Do While loop)

The Question

Why does the behaviour described in the problem section (above) occur, and why is it inconsistent with equivalent structures?

The For Loop Macro

Sub InsertSurnames()

    Dim LastRow As Long
    Dim r As Long
    Dim surname As String
    Dim tickets As Integer
    Dim surnameCol As Integer
    Dim ticketCol As Integer
    Dim targetCol As Integer

    surnameCol = 1
    ticketCol = 3
    targetCol = 4
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

    For r = 1 To LastRow

        surname = Cells(r, surnameCol).Value
        tickets = Cells(r, ticketCol).Value


        If (Not (Len(surname) = 0)) Then

            Cells(r, targetCol).Value = surname

            For x = 1 To tickets - 1

                Cells(r + x, 1).EntireRow.Insert
                Cells(r + x, targetCol).Value = surname



            Next x

            LastRow = LastRow + tickets - 1

        End If


    Next r

End Sub

The Do While Loop Macro

Sub InsertSurnames()

    Dim LastRow As Long
    Dim r As Long
    Dim surname As String
    Dim tickets As Integer
    Dim surnameCol As Integer
    Dim ticketCol As Integer
    Dim targetCol As Integer

    surnameCol = 1
    ticketCol = 3
    targetCol = 4
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    r = 1

    Do While r <= LastRow

        surname = Cells(r, surnameCol).Value
        tickets = Cells(r, ticketCol).Value


        If (Not (Len(surname) = 0)) Then

            Cells(r, targetCol).Value = surname

            For x = 1 To tickets - 1

                Cells(r + x, 1).EntireRow.Insert
                Cells(r + x, targetCol).Value = surname



            Next x

            LastRow = LastRow + tickets - 1

        End If

        r = r + 1
    Loop

End Sub

Answer 1

This is by design in VB. The limit of a for loop is calculated only once and saved in a temporary variable, just before the start of the loop. So if you change the value of the variables from which it calculates the limit, the temp variable is not affected. While this has the unexpected effect you encountered, it has the plus that the limit is only calculated once, so any methods used in this calculation is only entered once, potentially doing the loop faster.

Answer 2

The compiler interperets the 'For' Loop construct differently, and uses different assembly calls to place the temporary variable into CPU cache, so after each iteration it doesn't need to go back out to RAM to read the variable, it can just grab it from the cpu's cache. This is by design to increase performance, thats why 'For' loops are generally faster than 'While' loops. The limit variable for the 'for' loop still lives in memory, but its not reading it during each iteration. So if you change the variable used to originally set the upper bound, your loop will still run to the original bound you set it to. While loops check its exit clause at each iteration, and does not cache is variable. Generally 'For' loops should be used when you have a set amount of iterations, as opposed to the while loop when you are not sure how many times you will need to loop, and need more dynamic control.

Answer 3

To keep using the For...Next loop, you could do:

For r = LastRow To 1 Step -1
    surname = Cells(r, surnameCol).Value
    tickets = Cells(r, ticketCol).Value

    If (Not (Len(surname) = 0)) Then
        Cells(r, targetCol).Value = surname

        For x = 1 To tickets - 1
            Cells(r + x, 1).EntireRow.Insert
            Cells(r + x, targetCol).Value = surname
        Next x

        LastRow = LastRow + tickets - 1
    End If
Next r

Any time that you want to insert or delete rows on a Worksheet from inside a loop, it's usually better to start at the end and work backwards. This means that you don't have to adjust your loop index in most cases