excel - 动态搜索 ListObjects 之间的缺失值

Question

我想遍历Table1的一列，如果Table2的同一列名中缺少该值，则将其插入Table2的末尾。
下面的代码从 Table1 中插入确切的行数，但它只插入最后一行的值。
例如表格1:


列名


值(value)1

值(value)2

值(value)3

值(value)4


运行宏后，Table2 如下所示:


列名


值(value)4

值(value)4

值(value)4

值(value)4

Public Sub FindingMissingValues()
    Dim SourceTable As ListObject
    Dim TargetTable As ListObject
    Dim rngDataCell As Range
    
    Set SourceTable = Sheet1.ListObjects("Table1")
    Set TargetTable = Sheet2.ListObjects("Table2")
       
    For Each rngDataCell In SourceTable.ListColumns("Column Name").DataBodyRange.Rows
        If TargetTable.ListColumns("Column Name").DataBodyRange.Find(rngDataCell.Value, , , xlWhole) Is Nothing Then
            TargetTable.ListColumns("Column Name").DataBodyRange.Offset(1).Value = rngDataCell.Value
        End If
    Next rngDataCell
End Sub

似乎它不搜索特定的单元格值。你能告诉我，我做错了什么吗？

Answer 1

尝试这个。根据评论，认为您首先在第二个表中添加一行，然后将值插入该列的底行。
有一个很好的表格指南here .

Public Sub FindingMissingValues()

Dim SourceTable As ListObject
Dim TargetTable As ListObject
Dim rngDataCell As Range

Set SourceTable = Sheet1.ListObjects("Table1")
Set TargetTable = Sheet2.ListObjects("Table2")
   
For Each rngDataCell In SourceTable.ListColumns("Column Name").DataBodyRange.Rows
    If TargetTable.ListColumns("Column Name").DataBodyRange.Find(rngDataCell.Value, , , xlWhole) Is Nothing Then
        TargetTable.ListRows.Add 'adds row at bottom of table
        TargetTable.ListColumns("Column Name").DataBodyRange.Cells(TargetTable.DataBodyRange.Rows.Count).Value = rngDataCell.Value
    End If
Next rngDataCell

End Sub