|
发表于 2015-3-20 09:07
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, i&, d
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- [i1:k10000] = ""
- arr = Range("a1:h" & [a65536].End(3).Row)
- ReDim brr(1 To UBound(arr), 1 To 3)
- For i = UBound(arr) To 3 Step -1
- x = CStr(arr(i, 1))
- If InStr(x, "部分") = 0 Then
- If d.exists(x) Then brr(i, 1) = d(x)
- If d1.exists(x) Then brr(i, 2) = d1(x)
- If Len(arr(i, 6)) Then
- brr(i, 1) = arr(i, 6) * arr(i, 7): s = s + brr(i, 1)
- brr(i, 2) = arr(i, 6) * arr(i, 8): s1 = s1 + brr(i, 2)
- End If
- If Len(x) > 2 Then '长度大于2,上一级自动汇总
- yrr = Split(x, "."): xl = Len(yrr(UBound(yrr))) '最后一部分长度
- y = Left(x, Len(x) - xl - 1) '去掉最后一部分,到上一级
- d(y) = d(y) + brr(i, 1)
- d1(y) = d1(y) + brr(i, 2)
- End If
- Else
- d.RemoveAll: d1.RemoveAll
- brr(i, 1) = s: brr(i, 2) = s1
- zs = zs + s: s = 0
- zs1 = zs1 + s1: s1 = 0
- End If
- brr(i, 3) = brr(i, 1) + brr(i, 2)
- Next
- brr(2, 1) = zs: brr(2, 2) = zs1: brr(2, 3) = zs + zs1
- Range("i1").Resize(UBound(arr), 3) = brr
- Range("i1").Resize(1, 3) = Array("工程费(元)", "设备费(元)", "合计(元)")
- End Sub
复制代码 |
|