title: For Each Row in Range - Incorrect Row Iteration
body: <p>I'm creating a single-column index file from multi-row/-cell source data in Excel. I have two worksheets - a source and destination - in a single workbook. Iterating through each row (11 in dev, ~15,000 in prod) of the source worksheet the <code>For Each</code> loop I wrote should:</p>
  
  <ul>
  <li>Find the first empty cell in Column A of the destination worksheet</li>
  <li>Use <code>LastCell.Value =</code> to input a set value in that cell</li>
  <li>Use <code>LastCell.Offset(r, c).Value =</code> to pair a set value with a copied value from each cell in a single row of the source worksheet to the next destination cell down. There are eight to be copied and a ninth that is set</li>
  </ul>
  
  <p>The loop works perfectly <strong>once</strong>, then fails to iterate to the next row. Each subsequent loop shifts copied values up by one and leaves the set values, with blanks, in place. It does this once for each cell in the source row and only after that does it move on to the next row down.</p>
  
  <p>When I say set value I'm referring to what is in quotes, and copied values are those of the <code>Row.Offset</code>.</p>
  
  <p>The code is:</p>
  
  <pre><code>Sub Export_Index_File()
  
  Dim wb As Workbook
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim sr As Range
  
  Set wb = ActiveWorkbook
  Set ws1 = wb.Worksheets("Source Data")
  Set ws2 = wb.Worksheets("Index File")
  Set sr = ws1.Range("A2:H13")
  
  For Each Row In sr.Cells
      Set LastCell = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Offset(1)
      LastCell.Value = "Begin Doc"
      LastCell.Offset(1, 0).Value = "Account Number: " &amp; Row.Offset(0, 0).Value
      LastCell.Offset(2, 0).Value = "Account Type: " &amp; Row.Offset(0, 1).Value
      LastCell.Offset(3, 0).Value = "Date: " &amp; Row.Offset(0, 2).Value
      LastCell.Offset(4, 0).Value = "Doc Type: " &amp; Row.Offset(0, 3).Value
      LastCell.Offset(5, 0).Value = "File Path: " &amp; Row.Offset(0, 4).Value
      LastCell.Offset(6, 0).Value = "Institution: " &amp; Row.Offset(0, 5).Value
      LastCell.Offset(7, 0).Value = "Name: " &amp; Row.Offset(0, 6).Value
      LastCell.Offset(8, 0).Value = "TIN: " &amp; Row.Offset(0, 7).Value
      LastCell.Offset(9, 0).Value = "End Doc"
  Next Row
  
  End Sub
  </code></pre>
  
  <p>Here are two output examples. Successful on the left, unsuccessful on the right:</p>
  
  <pre><code>Begin Doc                  |Begin Doc
  Account Number: 123456     |Account Number: Checking
  Account Type: Checking     |Account Type:01/01/2001
  Date: 01/01/2001           |Date: Statement
  Doc Type: Statement        |Doc Type: 123456.pdf
  File Path: 123456.pdf      |File Path: 123
  Institution: 123           |Institution: Jane Doe
  Name: Jane Doe             |Name: 123-45-6789
  TIN: 123-45-6789           |TIN:
  End Doc                    |End Doc
  </code></pre>
  
  <p>Notice the one-line shift up. To fix this, I changed the loop to read <code>For Each Row In sr.Rows</code> but received Type 13 Mismatch errors. </p>
  
  <p>I copied this code from a working spreadsheet before expanding it to include more rows. It is nearly identical to the working code. What am I doing wrong?</p>