Option Explicit
Sub TEST6()
Dim ar(), br(), i&, j&, r&, n&, dic As Object, vKey
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
ar = [A3:B9].Value
n = WorksheetFunction.Sum(Application.Index(ar, , 2))
ReDim br(1 To n, 1 To 1)
For i = 1 To UBound(ar)
For j = 1 To ar(i, 2)
r = r + 1
br(r, 1) = ar(i, 1)
Next j
Next i
ReDim ar(1 To UBound(br), 1 To 3)
br = cutArray(br, 162)
r = 0: n = 0
For i = 1 To UBound(br)
dic.RemoveAll
For j = 1 To UBound(br(i))
dic(br(i)(j, 1)) = dic(br(i)(j, 1)) + 1
Next j
n = n + 1
For Each vKey In dic.keys
r = r + 1
ar(r, 1) = vKey
ar(r, 2) = n & "号箱"
ar(r, 3) = dic(vKey)
Next
Next i
Columns("E:G").ClearContents
[e2].Resize(, 3) = Split("规格型号 箱号 数量")
[e3].Resize(r, 3) = ar
Set dic = Nothing
Application.ScreenUpdating = True
Beep
End Sub
Function cutArray(ByVal ar, iCutNum&) As Variant
Dim br(), cr(), i&, j&, iPosRow&, r&, k&
For i = 1 To UBound(ar) Step iCutNum
iPosRow = IIf((i + iCutNum - 1) > UBound(ar), UBound(ar) Mod iCutNum, iCutNum)
ReDim cr(1 To iPosRow, 1 To UBound(ar, 2))
For j = 1 To UBound(cr)
For k = 1 To UBound(cr, 2)
cr(j, k) = ar(i - 1 + j, k)
Next k
Next j
r = r + 1
ReDim Preserve br(1 To r)
br(r) = cr
Next i
cutArray = br
End Function
|