vba 对于范围中的每一行 - 不正确的行迭代

For Each Row in Range - Incorrect Row Iteration

提问by UserUnknown

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 For Eachloop I wrote should:

我正在从 Excel 中的多行/单元格源数据创建一个单列索引文件。我有两个工作表 - 源和目标 - 在一个工作簿中。遍历源工作表的每一行（开发中为 11，生产中为 ~15,000），For Each我编写的循环应该：

• Find the first empty cell in Column A of the destination worksheet
• Use LastCell.Value =to input a set value in that cell
• Use LastCell.Offset(r, c).Value =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

• 在目标工作表的 A 列中找到第一个空单元格
• 用于LastCell.Value =在该单元格中输入设定值
• 用于LastCell.Offset(r, c).Value =将设置值与从源工作表单行中的每个单元格复制到下一个目标单元格的值配对。有八个要复制，第九个是设置

The loop works perfectly once, 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.

循环完美运行一次，然后无法迭代到下一行。每个后续循环将复制的值向上移动一个，并将设置的值保留在适当的位置，并留有空格。它对源行中的每个单元格执行一次，然后才向下移动到下一行。

When I say set value I'm referring to what is in quotes, and copied values are those of the Row.Offset.

当我说设置值时，我指的是引号中的内容，复制的值是Row.Offset.

The code is:

代码是：

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: " & Row.Offset(0, 0).Value
    LastCell.Offset(2, 0).Value = "Account Type: " & Row.Offset(0, 1).Value
    LastCell.Offset(3, 0).Value = "Date: " & Row.Offset(0, 2).Value
    LastCell.Offset(4, 0).Value = "Doc Type: " & Row.Offset(0, 3).Value
    LastCell.Offset(5, 0).Value = "File Path: " & Row.Offset(0, 4).Value
    LastCell.Offset(6, 0).Value = "Institution: " & Row.Offset(0, 5).Value
    LastCell.Offset(7, 0).Value = "Name: " & Row.Offset(0, 6).Value
    LastCell.Offset(8, 0).Value = "TIN: " & Row.Offset(0, 7).Value
    LastCell.Offset(9, 0).Value = "End Doc"
Next Row

End Sub

Here are two output examples. Successful on the left, unsuccessful on the right:

这是两个输出示例。左边成功，右边失败：

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

Notice the one-line shift up. To fix this, I changed the loop to read For Each Row In sr.Rowsbut received Type 13 Mismatch errors.

注意单行向上移动。为了解决这个问题，我将循环更改为 readFor Each Row In sr.Rows但收到了 Type 13 Mismatch 错误。

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?

在扩展它以包含更多行之前，我从工作电子表格中复制了此代码。它与工作代码几乎相同。我究竟做错了什么？

回答by UserUnknown

While I was proof-reading my post the answer dawned on me. I don't know how I missed this small error or what 'clicked' in my head to recognize it, but I answered my own question. I still feel a value in posting this in case others experience a similar problem.

当我校对我的帖子时，我突然想到了答案。我不知道我是怎么错过这个小错误的，或者我脑子里“点击”了什么来识别它，但我回答了我自己的问题。如果其他人遇到类似问题，我仍然觉得发布此内容很有价值。

The reason the code was looking at all eight cells in each row is because I told it to. While setting the variable for the source range, I mistakenly thought I needed to encompass all the data:

代码查看每行中的所有八个单元格的原因是因为我告诉它。在为源范围设置变量时，我错误地认为我需要包含所有数据：

Set sr = ws1.Range("A2:H13")

This is incorrect. I only need the first column and the code would sort out the rest of the cells using my Offsetcommands. The working code is:

这是不正确的。我只需要第一列，代码将使用我的Offset命令整理出其余的单元格。工作代码是：

 Set sr = ws1.Range("A2:A13")