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.

The Spreadsheet http://s3.amazonaws.com/twitpic/photos/full/120237739.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&Expires=1277404609&Signature=pGRx%2Fxcm3InEY2PyKd3k09hC7Xo%3D

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

The Spreadsheet after changes http://s3.amazonaws.com/twitpic/photos/full/120238390.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&Expires=1277404546&Signature=xrUAdzyIJWKGnrge%2FCD4EudiyX8%3D

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

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.