|
用字典记录Raw data中行列标签的位置,再根据Target表标签查字典。
Sub test()
Dim r%, i%
Dim arr, brr, Crr()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Worksheets("raw data")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
c = .Cells(5, .Columns.Count).End(xlToLeft).Column
brr = .Range("a5").Resize(r, c).Value
End With
For i = 2 To UBound(brr)
For j = 2 To UBound(brr, 2)
d(brr(i, 1) & brr(1, j)) = brr(i, j)
Next
Next
With Worksheets("target")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
c = .Cells(5, .Columns.Count).End(xlToLeft).Column
arr = .Range("a5").Resize(r, 1).Value
brr = .Range("a5").Resize(1, c).Value
ReDim Crr(2 To r, 2 To c)
For i = 2 To r - 4
For j = 2 To c
If d.exists(arr(i, 1) & brr(1, j)) Then
Crr(i, j) = d(arr(i, 1) & brr(1, j))
End If
Next
Next
.Range("b6").Resize(r - 5, c - 1).Value = Crr
End With
End Sub
|
|