Hi,

I am trying to create model where capacity smoothing would be done with VBA code.

I have simple data set:

Left: Fruits with quantities in the weeks.
Right: Summary table

Limit ---> 40 40 40 40 40
Week 1 Week 2 Week 3 Week 4 Week 5 Week 1 Week 2 Week 3 Week 4 Week 5
Banana 18 18 Totals 64 0 67 0 0
Apple 14 15
Pear 19 15
Orange 13 19



Logic: If total amount of fruits in the week is more than 40, than macro would go through each fruit and would figure out where is the next available space where to move the quantity, then would come back and checked if remain amount of fruits fit in to the 40 limit, if not then again macro would take quantity and move it to the next available space and so on.

For example - at this moment total amount of fruits in the first week is 64, which is greater than 40, so then I would take Banana quantity (18) and would figure out where is the next available space, which is Week 2, then after movement of bananas to week two, I would come back and re-checked if total amount of fruits in the week 1 fits. In this case after moving bananas there still more than 40, so I would take next fruit Apple (14) and would move it to next available space and so on... Hope it makes sense.

I can build simple loops, but I don't know how to check the result if it fits or not before actually moving the cell value.

Thanks


I have attached excel file Capacity smoothing.xlsx