Complicated sum
I didn't know if you could combine multiple rows. I removed one section of
the code. Can I make the code for the multicomponent as the single
components? I supplied a 2nd macro below which just moves the output to
different column so I can make the two macro the same macro.
Sub CombineOrders()
With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
PreviousOrder = 0
For RowCount = 2 To LastRow
Order = .Range("B" & RowCount)
Order = Order + PreviousOrder
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then
'product matches
NextOrder = .Range("B" & (RowCount + 1))
If Order <= 400 Then
If Order + NextOrder <= 400 Then
PreviousOrder = Order
Else
.Range("C" & RowCount) = Order
End If
Else
PreviousOrder = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range("C" & RowCount) = Order
Else
.Range("C" & RowCount) = 400
.Range("D" & RowCount) = Order - 400
End If
PreviousOrder = 0
End If
Next RowCount
End With
End Sub
--------------------------------------------------------------------------------------------
same code with diffeent outputs
Sub CombineOrders()
With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
PreviousOrder = 0
For RowCount = 2 To LastRow
Product = .Range("A" & RowCount)
NextProduct = .Range("A" & (RowCount + 1))
Order = .Range("B" & RowCount)
Order = Order + PreviousOrder
If Product = NextProduct Then
'product matches
NextOrder = .Range("B" & (RowCount + 1))
If Order <= 400 Then
If Order + NextOrder <= 400 Then
PreviousOrder = Order
Else
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = Order
End If
Else
PreviousOrder = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = Order
Else
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = 400
.Range("G" & RowCount) = Product
.Range("H" & RowCount) = Order - 400
End If
PreviousOrder = 0
End If
Next RowCount
End With
End Sub
|