planning : combinaisons possibles

**ericdev67** · 07/12/2010, 22h26

bonjour,

Je cherche à créer le "planning parfait" et surtout que VBA me calcule toutes les possibilités......

Je mets en PJ le format de tableau que j'utilise. Mon code actuel est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
Sub xes()
 
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As Integer
Dim i6 As Integer
Dim i7 As Integer
Dim i8 As Integer
Dim i9 As Integer
Dim i10 As Integer
Dim i11 As Integer
Dim i12 As Integer
Dim i13 As Integer
Dim i14 As Integer
Dim i15 As Integer
Dim i16 As Integer
Dim i17 As Integer
Dim i18 As Integer
Dim i19 As Integer
Dim i20 As Integer
Dim i21 As Integer
Dim i22 As Integer
Dim i23 As Integer
Dim i24 As Integer
Dim i25 As Integer
Dim i26 As Integer
Dim i27 As Integer
Dim a(5)
For i = 1 To 5
a(i) = Cells(i + 1, 37)
Next
For i1 = 1 To 5
For i2 = 1 To 5
For i3 = 1 To 5
For i4 = 1 To 5
For i5 = 1 To 5
For i6 = 1 To 5
For i7 = 1 To 5
For i8 = 1 To 5
For i9 = 1 To 5
For i10 = 1 To 5
For i11 = 1 To 5
For i12 = 1 To 5
For i13 = 1 To 5
For i14 = 1 To 5
For i15 = 1 To 5
For i16 = 1 To 5
For i17 = 1 To 5
For i18 = 1 To 5
For i19 = 1 To 5
For i20 = 1 To 5
For i21 = 1 To 5
For i22 = 1 To 5
For i23 = 1 To 5
For i24 = 1 To 5
For i25 = 1 To 5
For i26 = 1 To 5
For i27 = 1 To 5
 
 
If Not (i1 = i10 Or i2 = i11 Or i3 = i12 Or i4 = i13 Or i5 = i14 Or i6 = i15 Or i7 = i16 Or i8 = i17 Or i9 = i18) Then
If Not (i1 = i19 Or i2 = i20 Or i3 = i21 Or i4 = i22 Or i5 = i23 Or i6 = i24 Or i7 = i25 Or i8 = i26 Or i9 = i27) Then
 
Cells(5, 3) = i1
Cells(6, 3) = i10
Cells(7, 3) = i19
Cells(8, 3) = i28
Cells(9, 3) = i37
Cells(10, 3) = i46
Cells(11, 3) = i55
Cells(12, 3) = i64
Cells(13, 3) = i73
Cells(14, 3) = i82
Cells(15, 3) = i91
Cells(16, 3) = i100
Cells(5, 5) = i2
Cells(6, 5) = i11
Cells(7, 5) = i20
Cells(8, 5) = i29
Cells(9, 5) = i38
Cells(10, 5) = i47
Cells(11, 5) = i56
Cells(12, 5) = i65
Cells(13, 5) = i74
Cells(14, 5) = i83
Cells(15, 5) = i92
Cells(16, 5) = i101
Cells(5, 7) = i3
Cells(6, 7) = i12
Cells(7, 7) = i21
Cells(8, 7) = i30
Cells(9, 7) = i39
Cells(10, 7) = i48
Cells(11, 7) = i57
Cells(12, 7) = i66
Cells(13, 7) = i75
Cells(14, 7) = i84
Cells(15, 7) = i93
Cells(16, 7) = i102
Cells(5, 9) = i4
Cells(6, 9) = i13
Cells(7, 9) = i22
Cells(8, 9) = i31
Cells(9, 9) = i40
Cells(10, 9) = i49
Cells(11, 9) = i58
Cells(12, 9) = i67
Cells(13, 9) = i76
Cells(14, 9) = i85
Cells(15, 9) = i94
Cells(16, 9) = i103
Cells(5, 11) = i5
Cells(6, 11) = i14
Cells(7, 11) = i23
Cells(8, 11) = i32
Cells(9, 11) = i41
Cells(10, 11) = i50
Cells(11, 11) = i59
Cells(12, 11) = i68
Cells(13, 11) = i77
Cells(14, 11) = i86
Cells(15, 11) = i95
Cells(16, 11) = i104
Cells(5, 13) = i6
Cells(6, 13) = i15
Cells(7, 13) = i24
Cells(8, 13) = i33
Cells(9, 13) = i42
Cells(10, 13) = i51
Cells(11, 13) = i60
Cells(12, 13) = i69
Cells(13, 13) = i78
Cells(14, 13) = i87
Cells(15, 13) = i96
Cells(16, 13) = i105
Cells(5, 15) = i7
Cells(6, 15) = i16
Cells(7, 15) = i25
Cells(8, 15) = i34
Cells(9, 15) = i43
Cells(10, 15) = i52
Cells(11, 15) = i61
Cells(12, 15) = i70
Cells(13, 15) = i79
Cells(14, 15) = i88
Cells(15, 15) = i97
Cells(16, 15) = i106
Cells(5, 17) = i8
Cells(6, 17) = i17
Cells(7, 17) = i26
Cells(8, 17) = i35
Cells(9, 17) = i44
Cells(10, 17) = i53
Cells(11, 17) = i62
Cells(12, 17) = i71
Cells(13, 17) = i80
Cells(14, 17) = i89
Cells(15, 17) = i98
Cells(16, 17) = i107
Cells(5, 19) = i9
Cells(6, 19) = i18
Cells(7, 19) = i27
Cells(8, 19) = i36
Cells(9, 19) = i45
Cells(10, 19) = i54
Cells(11, 19) = i63
Cells(12, 19) = i72
Cells(13, 19) = i81
Cells(14, 19) = i90
Cells(15, 19) = i99
Cells(16, 19) = i108
 
stop
 
End If
End If
 
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
 
End Sub

très (trop) lourd, je l'accorde. C'est d'ailleurs pour ça que je l'ai limité au 3 premiers noms...et déjà là, le code rame fort

Le principe est simple : il faut calculer pour chaque demi-journée et pour chaque nom, les combinaisons possibles, d'où l'escalade des "For i....".

Peut-être que je fais fausse route...car ces escalades de "For i..." prennent beaucoup de temps...J'ai essayé avec une double variable style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part