|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
请问,以下代码如何简化?先谢谢各位大神。
sub 去重多个Item求和()
Dim brr, x
Dim d1 As Object
Set d1 = CreateObject("scripting.dictionary") '创建字典
Dim d2 As Object
Set d2 = CreateObject("scripting.dictionary") '创建字典
Dim d3 As Object
Set d3 = CreateObject("scripting.dictionary") '创建字典
Dim d4 As Object
Set d4 = CreateObject("scripting.dictionary") '创建字典
brr = Range("b4:g1000")
For x = 1 To UBound(brr)
d1(brr(x, 1)) = d1(brr(x, 1)) + brr(x, 3) 'key对应的item的值在原来的基础上加新的
d2(brr(x, 1)) = d2(brr(x, 1)) + brr(x, 4)
d3(brr(x, 1)) = d3(brr(x, 1)) + brr(x, 5)
d4(brr(x, 1)) = d4(brr(x, 1)) + brr(x, 6)
Next x
SHX.Range("B4:G1048576").ClearContents
SHX.Cells(4, 2).Resize(d1.Count) = Application.Transpose(d1.Keys)
SHX.Cells(4, 4).Resize(d1.Count) = Application.Transpose(d1.Items)
SHX.Cells(4, 5).Resize(d1.Count) = Application.Transpose(d2.Items)
SHX.Cells(4, 6).Resize(d1.Count) = Application.Transpose(d3.Items)
SHX.Cells(4, 7).Resize(d1.Count) = Application.Transpose(d4.Items)
end sub
|
|