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 Capacity smoothing.xlsx

[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.