Sub Arrangements()
Dim dur#, dif As Byte, nlig&, col%, t(), dico As Object
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte
Dim f As Byte, g As Byte, h As Byte, arr, i As Byte, n&, total&
dur = Now
dif = 6 'nombre de chiffres différents
nlig = 1000000
col = 0
ReDim t(nlig - 1, 0) 'base 0
Set dico = CreateObject("Scripting.Dictionary")
Rows("2:" & Rows.Count).ClearContents 'RAZ
For a = 0 To 9
For b = 0 To 9
For c = 0 To 9
For d = 0 To 9
For e = 0 To 9
For f = 0 To 9
For g = 0 To 9
For h = 0 To 9
arr = Array(a, b, c, d, e, f, g, h)
dico.RemoveAll
For i = 0 To 7
dico(arr(i)) = ""
If dico.Count = dif Then
If n = nlig Then
[A2].Offset(, col).Resize(n) = t
ReDim t(n - 1, 0) 'RAZ
total = total + n
n = 0: col = col + 1
End If
t(n, 0) = Join(arr)
n = n + 1
Exit For
End If
Next i, h, g, f, e, d, c, b, a
If n Then [A2].Offset(, col).Resize(n) = t
MsgBox total + n & " arrangements" & vbLf & _
"Durée " & Format(Now - dur, "hh:mm:ss")
End Sub
Sub Arrangements()
Dim dur#, dif As Byte, nlig&, col%, t(), dico As Object
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte
Dim f As Byte, g As Byte, arr, i As Byte, n&, total&
dur = Now
dif = 6 'nombre de chiffres différents
nlig = 1000000
col = 0
ReDim t(nlig - 1, 0) 'base 0
Set dico = CreateObject("Scripting.Dictionary")
Rows("2:" & Rows.Count).ClearContents 'RAZ
For a = 0 To 9
For b = 0 To 9
For c = 0 To 9
For d = 0 To 9
For e = 0 To 9
For f = 0 To 9
For g = 0 To 9
arr = Array(a, b, c, d, e, f, g)
dico.RemoveAll
For i = 0 To 6
dico(arr(i)) = ""
If dico.Count = dif Then
If n = nlig Then
[A2].Offset(, col).Resize(n) = t
ReDim t(n - 1, 0) 'RAZ
total = total + n
n = 0: col = col + 1
End If
t(n, 0) = Join(arr)
n = n + 1
Exit For
End If
Next i, g, f, e, d, c, b, a
If n Then [A2].Offset(, col).Resize(n) = t
MsgBox total + n & " arrangements" & vbLf & _
"Durée " & Format(Now - dur, "hh:mm:ss")
End Sub
Bonjour,
j'aimerai faire un tableau excel qui me donnerai toutes les combinaisons (ou arrangement il me semble) dans l'ordre sachant qu'il faut que je trouve un code à 8 chiffre avec 6 valeurs différentes. J’espère trouver de l'aide ! merci !
Sub Arrangements()
Dim dur#, dif As Byte, dossier$, col%, t(), dico As Object
Dim a As Byte, b As Byte, c As Byte, d As Byte, e As Byte
Dim f As Byte, g As Byte, h As Byte, arr, i As Byte, n&, total&, nom$
dur = Now
dif = 6 'nombre de chiffres différents
dossier = ThisWorkbook.Path & "\Arrangements\"
If Dir(dossier, vbDirectory) = "" Then MkDir dossier 'création du dossier
col = 1
ReDim t(1 To 50000, 1 To 20) 'base 1
Set dico = CreateObject("Scripting.Dictionary")
Feuil1.[B1:C1] = ""
Application.DisplayAlerts = False 'si un fichier a déjà été créé
For a = 0 To 9
For b = 0 To 9
For c = 0 To 9
For d = 0 To 9
For e = 0 To 9
For f = 0 To 9
For g = 0 To 9
For h = 0 To 9
arr = Array(a, b, c, d, e, f, g, h)
dico.RemoveAll
For i = 0 To 7
dico(arr(i)) = ""
If dico.Count = dif Then
n = n + 1
t(n, col) = Join(arr, "")
If n = 50000 Then
total = total + n
n = 0: col = col + 1
End If
If (total + n) Mod 1000000 = 0 Then
col = 1
Application.ScreenUpdating = False
Workbooks.Add xlWBATWorksheet
[A1:T50000].NumberFormat = "@"
[A1:T50000].HorizontalAlignment = xlCenter
[A1:T50000] = t
ReDim t(1 To 50000, 1 To 20) 'RAZ
nom = "Mio " & Format(total / 1000000, "00") & " - "
Feuil1.[B1] = nom & [A1]
ActiveSheet.Name = nom & [A1]
ActiveWorkbook.SaveAs dossier & nom & [A1]
ActiveWorkbook.Close
Feuil1.[C1] = Now - dur
Application.ScreenUpdating = True
DoEvents
End If
Exit For
End If
Next i, h, g, f, e, d, c, b, a
Application.ScreenUpdating = True
Workbooks.Add xlWBATWorksheet
[A1:T50000].NumberFormat = "@"
[A1:T50000].HorizontalAlignment = xlCenter
[A1:T50000] = t
ActiveSheet.Name = nom & [A1]
ActiveWorkbook.SaveAs dossier & nom & [A1]
ActiveWorkbook.Close
Feuil1.[B1] = total + n: Feuil1.[C1] = Now - dur
Application.ScreenUpdating = True
DoEvents
MsgBox total + n & " arrangements" & vbLf & _
"Durée " & Format(Now - dur, "hh:mm:ss")
End Sub
Il est quand même possible de lister les 58 968 000 arrangements pour 8 chiffres.
pour simplement vérifier si la valeur saisie fait partie des codes correspondant aux critères souhaités ???Quel intérêt de générer toutes les combinaisons possibles ????
un code à 8 chiffre avec 6 valeurs différentes
Function VerifCode$(code$)
Dim d As Object, i As Byte
If Not code Like "########" Then VerifCode = "???": Exit Function
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To 8
d(Mid(code, i, 1)) = ""
Next
If d.Count < 6 Then VerifCode = d.Count & " distincts"
End Function
Private Sub Workbook_Open()
Set dico = CreateObject("Scripting.Dictionary")
End Sub
Public dico As Object 'mémorise la variable
Function VerifCodeRapide$(code$)
Dim i As Byte
If Not code Like "########" Then VerifCodeRapide = "???": Exit Function
dico.RemoveAll 'RAZ
For i = 1 To 8
dico(Mid(code, i, 1)) = ""
If dico.Count = 6 Then Exit Function
Next
VerifCodeRapide = dico.Count & " distincts"
End Function
Sub Arrangements()
Dim dur#, dif, nlig&, col%, t()
Dim a, b, c, d, e, f, g, n1, n2, n3, n4, n5, n6
Dim x1$, x2$, x3$, x4$, x5$, x6$, n&, total&
dur = Now
dif = 6 'nombre de chiffres différents
nlig = 1000000
col = 0
ReDim t(nlig - 1, 0) 'base 0
Rows("2:" & Rows.Count).ClearContents 'RAZ
For a = 0 To 9
For b = 0 To 9
n1 = 1 - (a <> b)
x1 = a & b
If n1 < dif - 5 Then GoTo 1
For c = 0 To 9
n2 = n1 - (InStr(x1, c) = 0)
x2 = x1 & c
If n2 < dif - 4 Then GoTo 2
For d = 0 To 9
n3 = n2 - (InStr(x2, d) = 0)
x3 = x2 & d
If n3 < dif - 3 Then GoTo 3
For e = 0 To 9
n4 = n3 - (InStr(x3, e) = 0)
x4 = x3 & e
If n4 < dif - 2 Then GoTo 4
For f = 0 To 9
n5 = n4 - (InStr(x4, f) = 0)
x5 = x4 & f
If n5 < dif - 1 Then GoTo 5
For g = 0 To 9
n6 = n5 - (InStr(x5, g) = 0)
x6 = x5 & g
If n6 < dif Then GoTo 6
If n = nlig Then
[A2].Offset(, col).Resize(n) = t
ReDim t(n - 1, 0) 'RAZ
total = total + n
n = 0: col = col + 1
End If
t(n, 0) = x6
n = n + 1
6 Next g
5 Next f
4 Next e
3 Next d
2 Next c
1 Next b
Next a
If n Then [A2].Offset(, col).Resize(n) = t
MsgBox total + n & " arrangements" & vbLf & _
"Durée " & Format(Now - dur, "hh:mm:ss")
End Sub