|
发表于 2014-12-31 14:00
|
显示全部楼层
本楼为最佳答案
- Sub tt()
- arr = Sheet1.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary") '姓名+项目
- Set d1 = CreateObject("scripting.dictionary") '姓名
- Set d2 = CreateObject("scripting.dictionary") '项目
- xmonth = [a2]
- For i = 2 To UBound(arr)
- If arr(i, 1) = xmonth Then
- d1(arr(i, 2)) = ""
- d2(arr(i, 3)) = ""
- d(arr(i, 2) & arr(i, 3)) = d(arr(i, 2) & arr(i, 3)) + arr(i, 4)
- End If
- Next
- [b5:b100,c4:n100].ClearContents
- If d.Count = 0 Then MsgBox "无记录": Exit Sub
- [b5].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
- [c4].Resize(1, d2.Count) = d2.keys
- brr = [b4].CurrentRegion
- For i = 2 To UBound(brr)
- For j = 2 To UBound(brr, 2)
- brr(i, j) = d(brr(i, 1) & brr(1, j))
- Next
- Next
- [b4].CurrentRegion = brr
- End Sub
复制代码 |
|