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.

Part	Total Minutes	WK1	WK2	WK3	WK4	WK5
1	280	280
2	2079	1640	439
3	4200		1481	1920	799
4	2552				1121	1431
5	396					396
6	7134					93
7	1476
8	2288
	Capacity	1920	1920	1920	1920	1920

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[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