本帖最后由 lidayu 于 2017-6-30 15:43 编辑
请老师赐教红色代码能否简化
Sub 汇总()
Set d = CreateObject("scripting.dictionary")
arr = Sheets("数据源").[a1].CurrentRegion
For i = 2 To UBound(arr)
x = arr(i, 6) & arr(i, 8) & "软肩章" '& arr(i, 5)
y = arr(i, 6) & arr(i, 8) & "硬肩章" '& arr(i, 5)
Z = arr(i, 6) & "" & "套式肩章" '& arr(i, 5)
d(x) = d(x) + arr(i, 9)
d(y) = d(y) + arr(i, 9)
d(Z) = d(Z) + arr(i, 9)
Next
[b4:p25] = ""
arr = [a2:p25]
For i = 3 To UBound(arr) - 2
If arr(i, 1) = "" Then arr(i, 1) = arr(i - 1, 1)
For j = 2 To 12
If arr(1, j) = "" Then arr(1, j) = arr(1, j - 1)
x = arr(i, 1) & arr(2, j) & arr(1, j)
arr(i, j) = d(x)
arr(23, j) = arr(23, j) + arr(i, j)
arr(24, 2) = arr(24, 2) + arr(i, j)
Next
arr(i, 13) = arr(i, 2) + arr(i, 3) + arr(i, 4) + arr(i, 5) + arr(i, 6)
arr(i, 14) = arr(i, 7) + arr(i, 8) + arr(i, 9) + arr(i, 10) + arr(i, 11)
arr(i, 15) = arr(i, 12)
arr(i, 16) = arr(i, 13) + arr(i, 14) + arr(i, 15)
arr(23, 13) = arr(23, 13) + arr(i, 13)
arr(23, 14) = arr(23, 14) + arr(i, 14)
arr(23, 15) = arr(23, 15) + arr(i, 15)
Next
[a2:p25] = arr
ActiveWindow.DisplayZeros = False
End Sub
本帖最后由 大灰狼1976 于 2017-6-30 15:37 编辑
比如这句:arr(i, 13) = arr(i, 2) + arr(i, 3) + arr(i, 4) + arr(i, 5) + arr(i, 6)
改成:
For n = 2 To 6
m = m+ arr(i, n)
Next n
arr(i, 13) =m
其他类似。
|