|
发表于 2015-12-2 15:53
|
显示全部楼层
本楼为最佳答案
- Sub tt()
- Dim A, B, C
- arr = Range("a1:d" & [d65536].End(3).Row)
- Set d = CreateObject("scripting.dictionary")
- ReDim brr(1 To UBound(arr), 1 To 8)
- For i = 2 To UBound(arr)
- If Len(arr(i, 1)) = 0 Then arr(i, 1) = arr(i - 1, 1)
- bh = arr(i, 1)
- If Not d.exists(arr(i, 1)) Then
- n = n + 1
- brr(n, 1) = arr(i, 1)
- d(arr(i, 1)) = n
- End If
- x = arr(i, 4)
- p = d(arr(i, 1))
- If InStr(x, "宽") Then
- A = Val(Split(x, "宽")(1))
- If InStr(x, "*") Then B = Val(Split(x, "*")(1)) Else B = 1
- If InStr(x, "分") Then C = Val(Split(x, "分")(1)) Else C = 1
- brr(p, 2) = brr(p, 2) + 2 * B '装饰头
- If InStr(x, "双") Then brr(p, 3) = brr(p, 3) + 2 * B '堵头
- s = IIf(InStr(x, "单"), 4, 5)
- brr(p, s) = brr(p, s) + (C + 1) * B '单、双支架
- ss = IIf(InStr(x, "单"), 1, 2)
- brr(p, 6) = brr(p, 6) + Round(A * 8 * B * ss, 0) '吊环
- If C > 1 Then brr(p, 7) = brr(p, 7) + (C - 1) * B * ss '连接器
- End If
- Next
- With Sheet2
- .Cells.ClearContents
- .[a1].Resize(1, 8) = Array("序号", "装饰头", "堵头", "单杆支架", "双杆支架", "吊环", "连接器", "合计统计")
- For i = 1 To n
- For j = 2 To 7
- If brr(i, j) > 0 Then brr(i, 8) = brr(i, 8) & "," & .Cells(1, j) & ":" & brr(i, j)
- Next
- brr(i, 8) = Mid(brr(i, 8), 2)
- Next
- .[a2].Resize(n, 8) = brr
- .Activate
- End With
- End Sub
复制代码 |
|