Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionPrivate Sub OK_Click() 'Scrute les combinaisons possibles dont le total des valeurs dépasse la valeur cible 'et crée la liste. Application.ScreenUpdating = False 'Valeur du Mode par Machine (initialisation) Dim a, b, c, d, e, f, g, h a = 0 'L1000 b = 0 'L2000 c = 0 'L5000 d = 0 'L18000(1) e = 0 'L18000(2) f = 0 'N1 g = 0 'TR11 h = 0 'DL 'Etats Machines (initialisation) Dim i, j, k, l, m, n, o, p i 0 'i 1 à 5 (5 états L1000) j 0 'j 1 à 5 (5 états L2000) k 0 'k 1 à 5 (5 états L5000) l 0 'l 1 à 5 (5 états L18000-1) m 0 'm 1 à 5 (5 états L18000-2) n 0 'n 1 à 3 (3 états N1) o 0 'o 1 à 3 (3 états TR11) p 0 'p 1 à 2 (2 états DL) 'N° de 1ère Colonne de valeur Ampère concernée Dim q, r, s, t, u, v, w, x q = 3 r = 7 s = 11 t = 15 u = 19 v = 23 w = 25 x = 27 'RAZ '=== 'Efface tout pour les lignes au delà de 7 If Range("A65536").End(xlUp).Row > 7 Then last = Range("A65536").End(xlUp).Row Range("A8:AA" & last).ClearContents Range("A8:AA" & last).Interior.ColorIndex = xlNone Range("A8:AA" & last).Borders.LineStyle = xlNone Range("A8:AA" & last).Font.Bold = False End If 'L1000 'For i 1 To 5 a = 0 If i > 1 Then a = Cells(5, q + i - 2).Value z = a If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L2000 'For j 1 To 5 b = 0 If j > 1 Then b = Cells(5, r + j - 2).Value z = a + b If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L5000 'For k 1 To 5 c = 0 If k > 1 Then c = Cells(5, s + k - 2).Value z = a + b + c If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(1) 'For l 1 To 5 d = 0 If l > 1 Then d = Cells(5, t + l - 2).Value z = a + b + c + d If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(2) 'For m 1 To 5 e = 0 If m > 1 Then e = Cells(5, u + m - 2).Value z = a + b + c + d + e If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'N1 'For n 1 To 3 f = 0 If n > 1 Then f = Cells(5, v + n - 2).Value z = a + b + c + d + e + f If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'TR11 'For o 1 To 3 g = 0 If o > 1 Then g = Cells(5, w + o - 2).Value z = a + b + c + d + e + f + g If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'DL 'For p 1 To 2 h = 0 If p > 1 Then h = Cells(5, x + p - 2).Value z = a + b + c + d + e + f + g + h If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" If last / 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 If p > 1 Then Cells(last, x + p - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If Next p Next o Next n Next m Next l Next k Next j Next i 'FINALISATION 'last Range("A65536").End(xlUp).Row If last > 7 Then 'Style des lignes 8 à la dernière Range("A8:AA" & last).Borders.LineStyle = xlContinuous Range("A8:AA" & last).Font.Bold = True 'Nombre de scénarii trouvés Range("A7").Value = last - 7 & " cas de" & Chr(10) & "surcharge" Range("B5").Select 'Filtrage activé Range("C7:AA7").AutoFilter End If End SubPrivate Sub OK_Click() 'Scrute les combinaisons possibles dont le total des valeurs dépasse la valeur cible 'et crée la liste. Application.ScreenUpdating = False 'Valeur par Machine (initialisation) Dim a, b, c, d, e, f, g, h a = 0 'L1000 b = 0 'L2000 c = 0 'L5000 d = 0 'L18000(1) e = 0 'L18000(2) f = 0 'N1 g = 0 'TR11 h = 0 'DL 'Etats Machines (initialisation) Dim i, j, k, l, m, n, o, p i 0 'i 1 à 5 (5 états L1000) j 0 'j 1 à 5 (5 états L2000) k 0 'k 1 à 5 (5 états L5000) l 0 'l 1 à 5 (5 états L18000-1) m 0 'm 1 à 5 (5 états L18000-2) n 0 'n 1 à 3 (3 états N1) o 0 'o 1 à 3 (3 états TR11) p 0 'p 1 à 2 (2 états DL) 'N° de 1ère Colonne de valeur Ampère concernée Dim q, r, s, t, u, v, w, x q = 3 r = 7 s = 11 t = 15 u = 19 v = 23 w = 25 x = 27 'RAZ '=== 'Efface tout pour les lignes au delà de 7 If Range("A65536").End(xlUp).Row > 7 Then last = Range("A65536").End(xlUp).Row Range("A8:AA" & last).ClearContents Range("A8:AA" & last).Interior.ColorIndex = xlNone Range("A8:AA" & last).Borders.LineStyle = xlNone Range("A8:AA" & last).Font.Bold = False End If 'L1000 'For i 1 To 5 a = 0 If i > 1 Then a = Cells(5, q + i - 2).Value z = a If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L2000 'For j 1 To 5 b = 0 If j > 1 Then b = Cells(5, r + j - 2).Value z = a + b If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L5000 'For k 1 To 5 c = 0 If k > 1 Then c = Cells(5, s + k - 2).Value z = a + b + c If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(1) 'For l 1 To 5 d = 0 If l > 1 Then d = Cells(5, t + l - 2).Value z = a + b + c + d If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(2) 'For m 1 To 5 e = 0 If m > 1 Then e = Cells(5, u + m - 2).Value z = a + b + c + d + e If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'N1 'For n 1 To 3 f = 0 If n > 1 Then f = Cells(5, v + n - 2).Value z = a + b + c + d + e + f If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'TR11 'For o 1 To 3 g = 0 If o > 1 Then g = Cells(5, w + o - 2).Value z = a + b + c + d + e + f + g If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'DL 'For p 1 To 2 h = 0 If p > 1 Then h = Cells(5, x + p - 2).Value z = a + b + c + d + e + f + g + h If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" If last / 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 If p > 1 Then Cells(last, x + p - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If Next p Next o Next n Next m Next l Next k Next j Next i 'FINALISATION 'last Range("A65536").End(xlUp).Row If last > 7 Then 'Style des lignes 8 à la dernière Range("A8:AA" & last).Borders.LineStyle = xlContinuous Range("A8:AA" & last).Font.Bold = True 'Nombre de scénarii trouvés Range("A7").Value = last - 7 & " cas de" & Chr(10) & "surcharge" Range("B5").Select 'Filtrage activé Range("C7:AA7").AutoFilter End If End Sub
Cheyenne
As-tu pensé à représentation binaire de l'état des modes pour chaque machine ?
Private Sub OK_Click() Application.ScreenUpdating = False 'Valeur du Mode par Machine (initialisation) Dim a, b, c, d, e, f, g, h a = 0 'L1000 b = 0 'L2000 c = 0 'L5000 d = 0 'L18000(1) e = 0 'L18000(2) f = 0 'N1 g = 0 'TR11 h = 0 'DL 'Etats Machines (initialisation) Dim i, j, k, l, m, n, o, p i 0 'i 1 à 5 (5 états L1000) j 0 'j 1 à 5 (5 états L2000) k 0 'k 1 à 5 (5 états L5000) l 0 'l 1 à 5 (5 états L18000-1) m 0 'm 1 à 5 (5 états L18000-2) n 0 'n 1 à 3 (3 états N1) o 0 'o 1 à 3 (3 états TR11) p 0 'p 1 à 2 (2 états DL) 'N° de 1ère Colonne de valeur Ampère concernée Dim q, r, s, t, u, v, w, x q = 3 r = 7 s = 11 t = 15 u = 19 v = 23 w = 25 x = 27 'RAZ '=== 'Efface tout pour les lignes au delà de 7 If Range("A65536").End(xlUp).Row > 7 Then last = Range("A65536").End(xlUp).Row Range("A8:AA" & last).ClearContents Range("A8:AA" & last).Interior.ColorIndex = xlNone Range("A8:AA" & last).Borders.LineStyle = xlNone Range("A8:AA" & last).Font.Bold = False End If 'L1000 'For i 1 To 5 a = 0 If i > 1 Then a = Cells(5, q + i - 2).Value z = a If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L2000 'For j 1 To 5 b = 0 If j > 1 Then b = Cells(5, r + j - 2).Value z = a + b If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L5000 'For k 1 To 5 c = 0 If k > 1 Then c = Cells(5, s + k - 2).Value z = a + b + c If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(1) 'For l 1 To 5 d = 0 If l > 1 Then d = Cells(5, t + l - 2).Value z = a + b + c + d If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'L18000(2) 'For m 1 To 5 e = 0 If m > 1 Then e = Cells(5, u + m - 2).Value z = a + b + c + d + e If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'N1 'For n 1 To 3 f = 0 If n > 1 Then f = Cells(5, v + n - 2).Value z = a + b + c + d + e + f If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'TR11 'For o 1 To 3 g = 0 If o > 1 Then g = Cells(5, w + o - 2).Value z = a + b + c + d + e + f + g If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If 'DL 'For p 1 To 2 h = 0 If p > 1 Then h = Cells(5, x + p - 2).Value z = a + b + c + d + e + f + g + h If z > Range("B4").Value Then last = Range("A65536").End(xlUp).Row + 1 If i > 1 Then Cells(last, q + i - 2).Value = "X" If j > 1 Then Cells(last, r + j - 2).Value = "X" If k > 1 Then Cells(last, s + k - 2).Value = "X" If l > 1 Then Cells(last, t + l - 2).Value = "X" If m > 1 Then Cells(last, u + m - 2).Value = "X" If n > 1 Then Cells(last, v + n - 2).Value = "X" If o > 1 Then Cells(last, w + o - 2).Value = "X" If last / 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 If p > 1 Then Cells(last, x + p - 2).Value = "X" Range("A" & last).Value = "Scénrario" & last - 7 & ": " & z & "A" If last Mod 2 0 Then Range("A" & last & ":AA" & last).Interior.ColorIndex 15 Exit For End If End If Next p Next o Next n Next m Next l Next k Next j Next i 'FINALISATION 'last Range("A65536").End(xlUp).Row If last > 7 Then 'Style des lignes 8 à la dernière Range("A8:AA" & last).Borders.LineStyle = xlContinuous Range("A8:AA" & last).Font.Bold = True 'Nombre de scénarii trouvés Range("A7").Value = last - 7 & " cas de" & Chr(10) & "surcharge" Range("B5").Select 'Filtrage activé Range("C7:AA7").AutoFilter End If End Sub