-
VBA - capacity smoothing
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules