|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("代码表")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:g" & r)
- End With
- ReDim brr(1 To UBound(arr), 1 To 13)
- For i = 1 To UBound(arr)
- For j = 1 To 3
- brr(i, j) = arr(i, j)
- Next
- brr(i, 4) = arr(i, 6)
- brr(i, 5) = arr(i, 7)
- d(brr(i, 1)) = i
- Next
- k = 6
- For Each ws In Worksheets(Array("累计", "本月"))
- With ws
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:l" & r)
- For i = 1 To UBound(arr)
- If d.exists(arr(i, 2)) Then
- m = d(arr(i, 2))
- brr(m, k) = brr(m, k) + arr(i, 10)
- brr(m, k + 1) = brr(m, k + 1) + arr(i, 12)
- End If
- Next
- k = k + 2
- End With
- Next
- For i = 1 To UBound(brr)
- brr(i, 10) = brr(i, 5) + brr(i, 6) + brr(i, 8) - brr(i, 7) - brr(i, 9)
- Next
-
-
- fla = False
- For i = UBound(brr) - 1 To 1 Step -1
- bm = brr(i, 1)
- ReDim crr(4 To 13)
- j = i + 1
- Do While brr(j, 1) Like bm & "*"
- If brr(j, 1) Like bm & "###" Then
- For k = LBound(crr) To UBound(crr)
- crr(k) = crr(k) + brr(j, k)
- Next
- fla = True
- End If
- j = j + 1
- If j > UBound(brr) Then
- Exit Do
- End If
- Loop
- If fla Then
- For k = LBound(crr) To UBound(crr)
- brr(i, k) = crr(k)
- Next
- fla = False
- End If
- Next
- With Worksheets("汇总")
- .UsedRange.Offset(1, 0).Clear
- .Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
- r = 1 + UBound(brr)
- With .Range("a1:m1")
- .HorizontalAlignment = xlCenter
- End With
- With .UsedRange
- .VerticalAlignment = xlCenter
- End With
- With .Range("a2:a" & r)
- .HorizontalAlignment = xlLeft
- End With
- .Range("a1:m" & r).Borders.LineStyle = xlContinuous
- End With
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|