1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| Sub Plan()
Dim nOp As Long, nPr As Long, kOp As Long, kPr As Long
Dim kR As Long, kC As Long, kRfin As Long, d As Long, kRop As Long
nOp = 3 '--- nb opérations
nPr = 3 '--- nb Produits
ThisWorkbook.Activate
Worksheets("Plan").Select
kRfin = WorksheetFunction.CountA(Rows("1:" & nOp * nPr)) - nOp * nPr
For kC = 3 To kRfin
For kOp = 1 To nOp
d = 0
kRop = 0
'--- détermine nouvelle production à lancer possible
For kR = kOp To kRfin Step nOp
If Cells(kR, kC) <> "" Then
If Val(Cells(kR, kC - 1)) > 0 Then
d = Val(Cells(kR, kC))
kRop = kR
ElseIf Val(Cells(kR, kC)) > d Then
d = Val(Cells(kR, kC))
kRop = kR
End If
End If
Next kR
'--- si production à lancer,
'--- pousser celles qui auraient également pu démarrer au même moment
If kRop > 0 Then
For kR = kOp To kRfin Step nOp
If kR <> kRop And Cells(kR, kC) <> "" Then
Range(Cells(kR, kC), Cells(kR + nOp - kOp, kC + d - 1)).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next kR
End If
Next kOp
Next kC
End Sub |
Partager