- Sub Macro1()
- On Error Resume Next '纠错处理,忽略空值
- Dim arr, brr, d, i&, j&, k&, s&
- Set d = CreateObject("scripting.dictionary") '创建字典对象
- Sheet1.Activate
- arr = Range("a1").CurrentRegion '数组arr
- ReDim brr(1 To UBound(arr) - 3, 1 To UBound(arr, 2)) '数组brr
- For i = 4 To UBound(arr)
- '如单位名称不存在,加入字典,对应s值,并把列对应的值赋值数组brr
- If Not d.exists(arr(i, 2)) Then
- s = s + 1: d(arr(i, 2)) = s
- brr(s, 1) = s
- For j = 2 To UBound(arr, 2)
- brr(s, j) = arr(i, j)
- Next
- Else
- '如存在,找出对应的s值并累加
- For k = 3 To UBound(arr, 2)
- brr(d(arr(i, 2)), k) = brr(d(arr(i, 2)), k) + arr(i, k)
- Next
- End If
- Next
- Sheet2.Activate
- Cells.ClearContents
- Sheet1.[a1:p3].Copy [a1] '复制标题
- Range("a4").Resize(s, UBound(brr, 2)) = brr '填充数据
- End Sub
复制代码 |