|
本帖最后由 ligh1298 于 2013-6-1 18:19 编辑
- Sub 按人汇总()
- Dim i%, k%, j%, d As Object, arr()
- ReDim Preserve arr(1 To 42, 1 To 2)
- For i = 1 To 3
- a = Cells(65536, 2 * i - 1).End(3).Row
- For Each Rng In Range(Cells(2, 2 * i - 1), Cells(a, 2 * i - 1))
- If Rng <> "" Then
- k = k + 1
- arr(k, 1) = Cells(Rng.Row, 2 * i - 1)
- arr(k, 2) = Cells(Rng.Row, 2 * i)
- End If
- Next
- Next
- Set d = CreateObject("scripting.dictionary")
- For j = 1 To UBound(arr)
- If Not d.exists(arr(j, 1)) Then
- d.Add (arr(j, 1)), arr(j, 2)
- d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
- End If
- Next
- Cells(2, "h").Resize(d.Count, 1) = Application.Transpose(d.keys)
- Cells(2, "i").Resize(d.Count, 1) = Application.Transpose(d.items)
- End Sub
复制代码
- For j = 1 To UBound(arr)
- If Not d.exists(arr(j, 1)) Then
- d.Add (arr(j, 1)), arr(j, 2)
- Else
- d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
- End If
- Next
复制代码
|
|