title: Why doesn't this for loop process the full data set?
body: <p><strong>Background</strong></p>
  
  <p>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.</p>
  
  <p><a href="http://s3.amazonaws.com/twitpic/photos/full/120237739.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&amp;Expires=1277404609&amp;Signature=pGRx%2Fxcm3InEY2PyKd3k09hC7Xo%3D" rel="nofollow noreferrer">The Spreadsheet http://s3.amazonaws.com/twitpic/photos/full/120237739.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&amp;Expires=1277404609&amp;Signature=pGRx%2Fxcm3InEY2PyKd3k09hC7Xo%3D</a></p>
  
  <p>I need to change the spreadsheet so that each name is duplicated once per ticket on separate rows, like this:</p>
  
  <p><a href="http://s3.amazonaws.com/twitpic/photos/full/120238390.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&amp;Expires=1277404546&amp;Signature=xrUAdzyIJWKGnrge%2FCD4EudiyX8%3D" rel="nofollow noreferrer">The Spreadsheet after changes http://s3.amazonaws.com/twitpic/photos/full/120238390.png?AWSAccessKeyId=0ZRYP5X5F6FSMBCCSE82&amp;Expires=1277404546&amp;Signature=xrUAdzyIJWKGnrge%2FCD4EudiyX8%3D</a></p>
  
  <p>I have a macro to do this, however it exhibits strange behaviour</p>
  
  <p><strong>The Problem</strong></p>
  
  <p>The macro doesn't loop through the entire data set. Stepping through the code shows that, despite deliberately increasing the value of <code>LastRow</code>, the For loop only loops for however many times the original value specified. The new value of <code>LastRow</code> at the end of each iteration appears to be disregarded.</p>
  
  <p>This seems particularly odd as the equivalent Do While loop works fine (See below for the working code using a Do While loop)</p>
  
  <p><strong>The Question</strong></p>
  
  <p>Why does the behaviour described in the problem section (above) occur, and why is it inconsistent with equivalent structures?</p>
  
  <p><strong>The For Loop Macro</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>The Do While Loop Macro</strong></p>
  
  <pre><code>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 &lt;= 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
  </code></pre>