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 |
