Capacity Constraints and Scheduling...

[auballard]

I have a list of parts that has x amount of minutes to produce. I have a limited capacity each week. I would like to create an easy way to create a "matrix" that shows how the production minutes can be distributed. I need to be able to change the capacity as necessary.

[TABLE="width: 623"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Total Minutes[/TD]
[TD]WK1[/TD]
[TD]WK2[/TD]
[TD]WK3[/TD]
[TD]WK4[/TD]
[TD]WK5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]280[/TD]
[TD]280[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2079[/TD]
[TD]1640[/TD]
[TD]439[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4200[/TD]
[TD][/TD]
[TD]1481[/TD]
[TD]1920[/TD]
[TD]799[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2552[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1121[/TD]
[TD]1431[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]396[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]396[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7134[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1476[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2288[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Capacity[/TD]
[TD]1920[/TD]
[TD]1920[/TD]
[TD]1920[/TD]
[TD]1920[/TD]
[TD]1920[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-
NB:- This code should cover all column "WKs" that are applicable (i.e. WK columns in sheet)

[COLOR="Navy"]Sub[/COLOR] MG08Nov08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] hSum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] vSum1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vSum2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] ac = 1 To Lst
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
     [COLOR="Navy"]If[/COLOR] Not Dn.Address = Rng(Rng.Count).Address [COLOR="Navy"]Then[/COLOR]
        vSum1 = Abs(Application.Sum(Rng.Resize(Rng.Count - 1).Offset(, ac)))
        vSum2 = Abs(Rng(Rng.Count).Offset(, ac))
        hSum = Abs(Application.Sum(Dn.Offset(, 1).Resize(, Lst)))
        
        [COLOR="Navy"]If[/COLOR] Not vSum1 = vSum2 And Not hSum = Dn.Value [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] (Dn.Value - hSum) < (vSum2 - vSum1) [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, ac).Value = Dn.Value - hSum
            [COLOR="Navy"]Else[/COLOR]
                Dn.Offset(, ac).Value = vSum2 - vSum1
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[auballard]

Thank you Mick!

 

[MickG]

You're welcome