|
- Sub test()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Dim br(1 To 1, 1 To 5)
- With Sheets("大货")
- endrow = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- arr = .Range("A1:N" & endrow)
- For i = 3 To UBound(arr)
- If Not d.exists(arr(i, 1) & "," & arr(i, 10)) Then
- d(arr(i, 1) & "," & arr(i, 10)) = ""
- End If
- Next
- For Each k In d.keys
- For i = 3 To UBound(arr)
- If k = arr(i, 1) & "," & arr(i, 10) Then
- xp = xp + arr(i, 13): sf = sf + arr(i, 4)
- End If
- Next
- br(1, 1) = xp: br(1, 2) = sf
- d(k) = br
- Erase br
- Next
- End With
- With Sheets("补数")
- r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- arr = .Range("A1:C" & r)
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 1) & "," & arr(i, 2)) Then
- temp = arr(i, 3)
- br(1, 3) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = br
- Erase br
- Else
- tempar = d(arr(i, 1) & "," & arr(i, 2))
- temp = arr(i, 3)
- tempar(1, 3) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = tempar
- End If
- Next
- End With
- With Sheets("产前打样")
- r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- arr = .Range("A1:C" & r)
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 1) & "," & arr(i, 2)) Then
- temp = arr(i, 3)
- br(1, 4) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = br
- Erase br
- Else
- tempar = d(arr(i, 1) & "," & arr(i, 2))
- temp = arr(i, 3)
- tempar(1, 4) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = tempar
- End If
- Next
- End With
- With Sheets("工程打样")
- r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- arr = .Range("A1:C" & r)
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, 1) & "," & arr(i, 2)) Then
- temp = arr(i, 3)
- br(1, 5) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = br
- Erase br
- Else
- tempar = d(arr(i, 1) & "," & arr(i, 2))
- temp = arr(i, 3)
- tempar(1, 5) = temp
- d(arr(i, 1) & "," & arr(i, 2)) = tempar
- Erase br
- End If
- Next
- End With
- With Sheets("汇总")
- r = .Cells.Find("*", Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
- .[a5:H1000].ClearContents
- .Range("A5").Resize(d.Count) = Application.Transpose(d.keys)
- .Range("A5:A" & d.Count + 4).TextToColumns Destination:=Range("A5"), Comma:=True
- .Range("C5").Resize(d.Count, 5) = Application.Transpose(Application.Transpose(d.items))
- .Range("H5").Resize(d.Count).FormulaR1C1 = "=sum(rc[-5]:rc[-1])"
- End With
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|