Public Sub SplitListBy150()
Dim i As Long, pos As Long, vId As Long, vSum As Long
Dim pSource As Worksheet, pDest As Worksheet
Set pSource = ActiveSheet
Set pDest = Worksheets.Add
vId = 1&: vSum = 0&: pos = 1&
For i = 1& To 8&
If (vSum + pSource.Cells(i, 2&).Value) > 150& Then
pDest.Cells(pos, 1&).Value = pSource.Cells(i, 1&).Value
pDest.Cells(pos, 2&).Value = 150& - vSum
pDest.Cells(pos, 3&).Value = vId
vSum = pSource.Cells(i, 2&).Value - 150& + vSum
vId = vId + 1&: pos = pos + 2&
pDest.Cells(pos, 2&).Value = vSum
Else
pDest.Cells(pos, 2&).Value = pSource.Cells(i, 2&).Value
vSum = vSum + pSource.Cells(i, 2&).Value
End If
pDest.Cells(pos, 1&).Value = pSource.Cells(i, 1&).Value
pDest.Cells(pos, 3&).Value = vId
pos = pos + 1&
Next i
End Sub
|
