为什么for循环不处理完整的数据集?

背景

我有一个事件的票分配电子表格。电子表格的每一行上都有一个名称和分配的票据数量。

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

我需要更改电子表格,以便在不同的行中,每张票据的每个名称都重复一次,如下所示:

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

我有一个宏来做这个,但是它表现出奇怪的行为。

问题

宏不会循环整个数据集。单步执行代码表明,尽管故意增加了 LastRow ,for循环只循环指定的原始值的多次。新的价值 最后一行 在每次迭代结束时,似乎都被忽略了。

这看起来特别奇怪,因为等效的do-while循环工作正常(请参阅下面使用do-while循环的工作代码)。

问题

为什么出现问题部分(如上)所述的行为,为什么与等效结构不一致?

for循环宏

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

Do While循环宏

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