Consulting

Results 1 to 5 of 5

Thread: VBA - capacity smoothing

  1. #1

    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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,117
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    Quote Originally Posted by SamT View Post
    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

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,117
    Location
    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.
    Last edited by SamT; 02-17-2020 at 11:30 AM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Quote Originally Posted by SamT View Post
    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.

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
  •