|
Sub 汇总()
On Error Resume Next
Dim D
Dim R%, C%, Arr, Brr
Set D = CreateObject("scripting.dictionary") '创建字典
Arr = Sheet1.Range("A1").CurrentRegion '数组A引用表一单元格区域
For R = 2 To UBound(Arr)
D(Arr(R, 2) & Arr(R, 3) & Arr(R, 1)) = Arr(R, 4) '以表一中第2列、第3列、第1列的字符串作为字典的KEY,以第4列的值为字典的ITEM
Next R
Brr = Sheet2.Range("A2:H10") '数组B引用表二单元格区域
For R = 2 To UBound(Brr)
For C = 3 To UBound(Brr, 2)
Brr(R, C) = D(Brr(R, 1) & Brr(R, 2) & Brr(1, C)) 'C2:H10的每个单元格都在查找自己对应的编号、姓名及日期的ITEM,找到后填在数组B的对应位置。
Next C
Next R
Sheet2.Range("A2").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
End Sub
处理表格数据,数组和字典最常用,且速度非常快,建议在这两个方面多学习,蓝版的字典以及兰版的VBA80讲值得拥有。 |
|