Sub test()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Dim ar As Variant, br()
ReDim br(1 To 100, 1 To 10)
For Each sh In Sheets
If sh.Name <> "进度" And sh.Name <> "测试" Then
ar = sh.[a1].CurrentRegion
For i = 5 To UBound(ar)
If Trim(ar(i, 3)) <> "" And Trim(ar(i, 4)) <> "" And Trim(ar(i, 5)) <> "" Then
s = Trim(ar(i, 3)) & "|" & Trim(ar(i, 4)) & "|" & Trim(ar(i, 5))
t = d(s)
If t = "" Then
k = k + 1
d(s) = k
t = k
br(k, 1) = k
br(k, 2) = ar(i, 4)
br(k, 3) = ar(i, 5)
br(k, 4) = ar(i, 3)
End If
br(t, 5) = br(t, 5) + ar(i, 2)
br(t, 6) = br(t, 6) + ar(i, 6)
br(t, 8) = br(t, 8) + ar(i, 9)
End If
Next i
End If
Next sh
For i = 1 To k
If br(i, 6) = 0 Then
br(i, 7) = 0
Else
br(i, 7) = br(i, 6) / br(i, 5)
End If
br(i, 9) = br(i, 6) - br(i, 8)
Next i
With Sheets("测试")
.UsedRange.Offset(2).Clear
.[a3].Resize(k, UBound(br, 2)) = br
End With
MsgBox "ok!"
End Sub
|