VBA - capacity smoothing

[Ex-cel]

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 26005

[SamT]

for each week
Calc week Limit Average per Item (=10)
Set cel - First Item of Week

Do while week Total > Week Total limit
If Cel > CelLimitAverage (= 10) Then
Set NextCel = Find next available space
NextCel = nextCel + (Cel - CelAverageLimit) (= 8)
Cel = CelLimitAverage
set Cel = Cel.offset(1)
End If
recalc Week Total
Loop
next week

[Ex-cel]

for each week
Calc week Limit Average per Item (=10)
Set cel - First Item of Week

Do while week Total > Week Total limit
If Cel > CelLimitAverage (= 10) Then
Set NextCel = Find next available space
NextCel = nextCel + (Cel - CelAverageLimit) (= 8)
Cel = CelLimitAverage
set Cel = Cel.offset(1)
End If
recalc Week Total
Loop
next week


Thanks SamT for reply.
I believe this piece of code is from other example with similar logic. Unfortunately this is a bit too complicated for me at this point. Would you be able to adjust it for my attached excel example, so then I can track the logic and learn.

Thanks

[SamT]

It's not CODE. It is an ALGORITHM. An algorithm is instructions on what to code for. Each line of instruction might take more than one line of code. For that matter, each step of an algorithm might require a separate PROCEDURE.

In your case, you have three tables, Weekly quantities, Weekly totals, and a non-assigned list of limits. It is implied that the limits change weekly and is not considered a permanent or semi-permanent value.

Once you see what the algorithm is telling you to code for, and, you understand your own BUSINESS RULES, then you can see how to restructure your data to make it easier to code. We at VBAX can't write the code because we don't know your business rules.

Even in the algorithm I posted, I assumed that your business rules allowed work to be averaged across all lines (Fruits). It may be a business requirement that partial orders per week are not allowed. It may be a business rule that some lines have their own limits.

[Ex-cel]

It's not CODE. It is an ALGORITHM. An algorithm is instructions on what to code for. Each line of instruction might take more than one line of code. For that matter, each step of an algorithm might require a separate PROCEDURE.

Thanks for explanation, will keep this in mind.