|
不是你没说清楚,是我没仔细看数据源!!
Sub AwTest()
Dim i&, c%, x&, r&, rqmc$, arr, brr(1 To 5000, 1 To 5), ws As Worksheet, d As Object
Set d = CreateObject("Scripting.Dictionary")
Sheet1.Activate
For Each ws In Sheets
If ws.Name <> ActiveSheet.Name Then
arr = ws.Range("A3:G" & ws.Cells(Rows.Count, 1).End(3).Row)
For i = 1 To UBound(arr)
If arr(i, 7) = "A公司" Then
c = 4
ElseIf arr(i, 7) = "B公司" Then
c = 5
Else
GoTo line:
End If
rqmc = arr(i, 1) & "|" & arr(i, 2)
x = d(rqmc)
If x = 0 Then r = r + 1: d(rqmc) = r: brr(r, 1) = arr(i, 1): brr(r, 2) = arr(i, 2): x = r
brr(x, c) = brr(x, c) + arr(i, 5)
brr(x, 3) = brr(x, 4) + brr(x, 5)
line:
Next
End If
Next
If r > 0 Then
[2:5000].ClearContents
[a2].Resize(r, 5) = brr
[a1].CurrentRegion.Sort key1:=[a1], order1:=1, key2:=[b1], order2:=1, Header:=xlYes
End If
End Sub |
|