|
楼主 |
发表于 2013-1-21 12:02
|
显示全部楼层
请优化代码
Sub xiaoji()
Dim hang As Integer, c As Range, j As Integer, arr(1 To 100), ct As Integer, ctmax As Integer
hang = Range("b65536").End(xlUp).Row
With Range("aa8:aa" & hang)
Set c = .Find("小计", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Row
ct = 1
arr(ct) = c.Row
ct = ct + 1
Set c = .FindNext(c)
Do
arr(ct) = c.Row
ct = ct + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress And c.Row <> arr(1)
End If
ctmax = ct - 2
ct = 1
For ct = 1 To ctmax
ctt = ct + 1
For j = 4 To 23
Cells(arr(ct), j) = WorksheetFunction.SumIf(Range(Cells(arr(ct), "aa"), Cells(arr(ctt), "aa")), "分项", Range(Cells(arr(ct), j), Cells(arr(ctt), j)))
Next j
Next ct
ct = ctmax + 1
For j = 4 To 23
Cells(arr(ct), j) = WorksheetFunction.SumIf(Range(Cells(arr(ct), "aa"), Cells(655365, "aa")), "分项", Range(Cells(arr(ct), j), Cells(655365, j)))
Next j
End With
End Sub |
|