Excel VBA：如何在循环例程的表中找到第一个空行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39859365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: How to find first empty row within a Table for a Loop routine?
提问by Jeremy
I reformatted a range of Sheets("Records")in a workbook as a Table (named "RecordsTable")to make it easier to do INDEX(MATCH,MATCH)functions for generating reports.... but now I screwed up my looping routine for filling that range from the input on Sheets("FORM").
我Sheets("Records")将工作簿中的一系列重新格式化为 aTable (named "RecordsTable")以便更轻松地执行INDEX(MATCH,MATCH)生成报告的功能......但现在我搞砸了我的循环例程来填充从Sheets("FORM").
It used to be:
它曾经是：
Set r = Sheets("Records").Range(A & Rows.Count).End(x1Up).Offset(1, 0)
i = 0
For Each c In Range("dataRange")
'dataRange is a list of cells to reference from the FORM input sheet
r.Offset(0, i).Value = Worksheets("FORM").Range(c)
i = i + 1
Next
However this code is now selecting the first row at the END of "RecordsTable"(row 501, as I defined 500 rows in my table) and inserting the data there.
但是，此代码现在选择 END 的第一行"RecordsTable"（第 501 行，因为我在表中定义了 500 行）并在那里插入数据。
I tried to change it to this:
我试着把它改成这样：
Set r = Sheets("Records").ListObjects("RecordsTable").DataBodyRange("A" & Rows.Count).End(x1Up).Offset(1, 0)
i = 0
For Each c In Range("dataRange")
r.Offset(0, i).Value = Worksheets("FORM").Range(c)
i = i + 1
Next
But this code is still selecting row 501 and making that row part of "RecordsTable".
How can I properly Set "r" to =the first empty row in "RecordsTable"?
但是此代码仍在选择第 501 行并使该行成为"RecordsTable". 如何正确Set "r" to =放置第一个空行"RecordsTable"？
For reference, Column "A" in "RecordsTable"has the header [INV #]. Also, when I step into the "Set r = ..."line, Rows.Countis returning a value of 1million+ (ie, total rows on the sheet) - if I understand this correctly, I want it to return a value of 500 (ie, total rows in table) - is that correct?
作为参考，Column "A" in "RecordsTable"有标题[INV #]。此外，当我进入该"Set r = ..."行时，Rows.Count返回的值是 100 万+（即工作表上的总行数）-如果我理解正确，我希望它返回 500 的值（即表中的总行数）-是那正确吗？
EDIT
编辑
"dataRange"is a single column list of cell references (I do have them labeled in column B, as @chrisneilsen suggest:
"dataRange"是单元格引用的单列列表（我确实将它们标记在 B 列中，正如@chrisneilsen 建议的那样：
A
一种
J6
J6
Y6
Y6
J8
J8
J10
J10
Y8
Y8
etc.
等等。
They are the cells on Sheets("FORM")that I need to pull data from and populate into my table, in the order indicated in "dataRange".
它们是Sheets("FORM")我需要按照 中指示的顺序从中提取数据并填充到表格中的单元格"dataRange"。
采纳答案by chris neilsen
Assuming you really have a Table, adding data to a Table (ListObject) using it's properties and methods:
假设你真的有一个表，使用它的属性和方法将数据添加到表（ListObject）：
Sub Demo()
Dim lo As ListObject
Dim c As Range
Set lo = Worksheets("Records").ListObjects("RecordsTable")
For Each c In Sheets("V").Range("dataRange")
If Not lo.InsertRowRange Is Nothing Then
lo.InsertRowRange.Cells(1, 1) = Sheets("FORM").Range(c)
Else
lo.ListRows.Add.Range.Cells(1, 1) = Sheets("FORM").Range(c)
End If
Next
End Sub
Note: looping a range on sheet Vand using that as a pointer to data on sheet FORM, copied from your answer - I'm assuming you know what you are doing here
注意：在工作表上循环一个范围V并将其用作指向工作表上数据的指针FORM，从您的答案中复制 - 我假设您知道您在这里做什么
Based on OP comment, adding data a single new row
基于 OP 注释，将数据添加到一个新行
Sub Demo()
Dim lo As ListObject
Dim c As Range, TableRange As Range
Dim i As Long
Set lo = Worksheetsheets("Records").ListObjects("RecordsTable")
If Not lo.InsertRowRange Is Nothing Then
Set TableRange = lo.InsertRowRange
Else
Set TableRange = lo.ListRows.Add.Range
End If
i = 1
For Each c In Sheets("V").Range("dataRange")
TableRange.Cells(1, i) = Sheets("FORM").Range(c)
i = i + 1
Next
End Sub
Note, this assumes that the order of the table columns is the same as the order of dataRange. It may be better to include table field names in dataRangeto avoid any mismatch issues
请注意，这假设表列的顺序与 的顺序相同dataRange。最好包含表字段名称dataRange以避免任何不匹配问题
As mentioned in updated OP, if column labels are in the next column, replace the Forloop with this (and add Dim r as Range, col as longto declarations)
如更新的 OP 中所述，如果列标签在下一列中，则For用此替换循环（并添加Dim r as Range, col as long到声明中）
For Each c In Sheets("V").Range("dataRange")
If Not c = vbNullString Then
Set r = Worksheets("FORM").Range(c.Value)
col = lo.ListColumns(c.Offset(, 1).Value).Index
TableRange.Cells(1, col) = r.Value
End If
Next
