|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
供参考。
- Sub 装箱()
- Dim nRow%, Arr(), Brr(), Crr(), nXs%, nSl%, nSum%, i%, m%, n%
- If Range("e3").Value <> "" Then
- nRow = Range("e2").End(xlDown).Row
- Range("e2:g" & nRow).ClearContents
- End If
- nRow = Range("a2").End(xlDown).Row
- Arr = Range("a1:b" & nRow).Value
- n = 1: nXs = WorksheetFunction.Sum(Range("b3:b" & nRow)) \ 162 + 1
- ReDim Crr(1 To nXs * 2, 1 To 3)
- For i = 3 To nRow
- Do While Arr(i, 2) > 0
- m = m + 1
- Crr(m, 1) = n & "号箱"
- Crr(m, 2) = Arr(i, 1)
- nSl = 162 - nSum
- If nSl > Arr(i, 2) Then nSl = Arr(i, 2)
- Crr(m, 3) = nSl
- nSum = nSum + nSl
- If nSum = 162 Then nSum = 0: n = n + 1
- Arr(i, 2) = Arr(i, 2) - nSl
- Loop
- Next
- Range("e2:g2").Value = Array("规格型号", "箱号", "数量")
- Range("e3").Resize(m, 3).Value = Crr
- End Sub
复制代码
|
|