关于此事我真的需要你的帮助!
我没有遇到任何错误,一切运行顺利......
我需要的是除了匹配 1 到 1 列之外,还需要匹配 2 到 2 列。
源表中的 A 列到目标表中的 G 列(已经在这里)我还想将源表中的 B 列添加到目标表中的 H 列。
摘要:将源表(A 和 B)中的 2 列与目标表(G 和 H)中的 2 列进行匹配。
希望您明白我的问题?谢谢!
代码的第一部分:没有问题
Sub Get_Data()
Const NUM_DATA_COLS As Long = 4
Dim SourceSheet As Worksheet, TargetSheet As Worksheet
Set SourceSheet = GetWorkbook(Source).Worksheets("Data")
Set TargetSheet = ThisWorkbook.Worksheets("Sheet2")
Dim SourceLastRow As Long, TargetLastRow As Long
SourceLastRow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
TargetLastRow = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim Primary_Fields(1 To NUM_DATA_COLS), Foreign_Fields(1 To NUM_DATA_COLS), n As Long
Dim i As Long, v, m
Dim rngPrimary_Key As Range
Dim Foreign_Key As Variant
代码的第二部分:
如何添加另一个 (rngPrimary_Key2) 和 (Foreign_Key2) 以将 2 列与 2 列匹配
我只匹配那些
设置 rngPrimary_Key = SourceSheet.Range("A2:A" & SourceLastRow) Foreign_Key = TargetSheet.Range("G2:G" & TargetLastRow).Value
我需要将它们添加到匹配条件中
设置 rngPrimary_Key2 = SourceSheet.Range("B2:B" & SourceLastRow)
Foreign_Key2 = TargetSheet.Range("H2:H" & TargetLastRow).Value
^- 如果这两列在两张表中匹配,则存储在数组中,而不是仅匹配一列。
Set rngPrimary_Key = SourceSheet.Range("A2:A" & SourceLastRow)
Foreign_Key = TargetSheet.Range("G2:G" & TargetLastRow).Value
For n = 1 To NUM_DATA_COLS
Primary_Fields(n) = SourceSheet.Range("B2:B" & SourceLastRow).Offset(0, n - 1).Value
Foreign_Fields(n) = EmptyCopy(Foreign_Key) 'empty array for results
Next n
' get matching rows and copy values to arrays
For i = LBound(Foreign_Key, 1) To UBound(Foreign_Key, 1)
v = Foreign_Key(i, 1)
m = Application.Match(v, rngPrimary_Key, 0)
If Not IsError(m) Then 'check got a match
For n = 1 To NUM_DATA_COLS
Foreign_Fields(n)(i, 1) = Primary_Fields(n)(m, 1)
Next n
End If
Next i
' / Write (Keys-IndexMatch) in Range offset Foreign_Field_1 2
Place2DArray TargetSheet.Range("H2"), Foreign_Fields(1)
Place2DArray TargetSheet.Range("i2"), Foreign_Fields(2)
Place2DArray TargetSheet.Range("J2"), Foreign_Fields(3)
Place2DArray TargetSheet.Range("K2"), Foreign_Fields(4)
End Sub
其余代码与我的问题无关
'return an empty array of same dimensions as 'arr'
Function EmptyCopy(arr)
Dim rv
ReDim rv(LBound(arr, 1) To UBound(arr, 1), LBound(arr, 2) To UBound(arr, 2))
EmptyCopy = rv
End Function
'copy a 1-based 2-d array 'arr' to a worksheet, starting at cell 'c'
Sub Place2DArray(c As Range, arr)
c.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub