|
楼主 |
发表于 2013-12-6 09:18
|
显示全部楼层
- Sub 总指标额度()
- Dim d1 As New Dictionary
- Dim d2 As New Dictionary
- Dim i As Long
- Dim m As Long
- Dim x As Long
- Dim arr, brr, crr()
- Dim t
- t = Timer
- Range("a6:aj65536").ClearContents
- brr = Range("b4:aj5")
- For m = 1 To 35
- If brr(1, m) = "" Then
- brr(1, m) = brr(1, m - 1)
- End If
- If d2.Exists(brr(1, m) & brr(2, m)) = False Then
- d2(brr(1, m) & brr(2, m)) = d2.Count + 2
- End If
- Next m
- With Sheets("数据录入")
- arr = .Range("A6:O" & .Range("B65536").End(xlUp).Row).Value
- End With
- For x = 1 To UBound(arr)
- If arr(x, 2) <> "" Then
- If Not d1.Exists(arr(x, 2)) Then
- i = i + 1
- d1(arr(x, 2)) = i
- ReDim Preserve crr(1 To 36, 0 To i)
- crr(1, i) = arr(x, 2)
- End If
- crr(2, d1(arr(x, 2))) = crr(2, d1(arr(x, 2))) + arr(x, 10)
- If arr(x, 13) = "年初预算" Then
- crr(8, d1(arr(x, 2))) = crr(8, d1(arr(x, 2))) + arr(x, 10)
- ElseIf arr(x, 13) = "上年结转" Then
- crr(11, d1(arr(x, 2))) = crr(11, d1(arr(x, 2))) + arr(x, 10)
- ElseIf arr(x, 13) = "中途追加" Then
- crr(36, d1(arr(x, 2))) = crr(36, d1(arr(x, 2))) + arr(x, 10)
- End If
- crr(d2(arr(x, 13) & arr(x, 14)), d1(arr(x, 2))) = crr(d2(arr(x, 13) & arr(x, 14)), d1(arr(x, 2))) + arr(x, 10)
- End If
- Next x
- i = i + 1
- ReDim Preserve crr(1 To 36, 0 To i)
- crr(1, 0) = "合 计"
- For x = 2 To 36
- For y = 1 To i
- crr(x, 0) = crr(x, 0) + crr(x, y)
- Next y, x
- With Sheets("总指标额度")
- .Range("A6:AJ65536").ClearContents
- .Range("A6:AJ65536").Borders.LineStyle = 0
- .Range("A6").Resize(UBound(crr, 2), UBound(crr)) = Application.Transpose(crr)
- .Range("A6").Resize(UBound(crr, 2), UBound(crr)).Borders.LineStyle = 1
- End With
- ' Cells(6, 1) = "合 计"
- ' For x = 2 To 36
- ' For y = 7 To i + 7
- 'Cells(6, x) = Cells(6, x) + Cells(y, x)
- ' Next y, x
- MsgBox "苏广余提示,汇总结束,用时" & Timer - t & "秒"
- End Sub
复制代码 自己弄好了 |
|