Sub toto()
Application.Calculation = xlCalculationManual
Dim i&, j&, k&, l&, m&, n&, Dat(1 To 200, 1 To 20), oColl As New Collection
Dim v2, V, w, X, y, Z, z3, an7, an8, an9, an10, an11, an12, an13, base1, v5, balise, an14, an15, an16, Difficult
V = Range("AN1").Value
w = Range("AN2").Value
X = Range("AN3").Value
y = Range("AN4").Value
Z = Range("AN5").Value
z3 = Range("AN6").Value
an7 = Range("AN7").Value
an8 = Range("AN8").Value
an9 = Range("AN9").Value
an10 = Range("AN10").Value
an11 = Range("AN11").Value
an12 = Range("AN12").Value
an13 = Range("AN13").Value
an14 = Range("AN14").Value
an15 = Range("AN15").Value
an16 = Range("AN16").Value
Difficult = Range("V2").Value
v5 = Range("AF6").Value
base1 = Range("AF5").Value
balise = Range("AK5").Value
base2 = Range("AG5").Value
balise2 = Range("AK6").Value
base3 = Range("AH5").Value
balise3 = Range("AM5").Value
Columns("A:F").ClearContents
For i = 1 To 200
Set oColl = Nothing
On Error Resume Next
With Range("W1:AJ1")
For j = 1 To .Count
oColl.Add .Cells(1, j).Value, CStr(.Cells(1, j).Value)
Next j
End With
l = oColl.Count
For j = 1 To 20
oColl.Add j, CStr(j)
Next j
On Error GoTo 0
m = oColl.Count
Randomize
For k = 1 To 6
n = l + 1 + Int((m - k - l + 1) * Rnd)
Dat(i, k) = oColl(n)
oColl.Remove n
Next k
v2 = i
If Difficult = "" And v2 = i And v5 = "" And base1 = "" And Not _
((Dat(i, 1) = V Or Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y) _
And (Dat(i, 2) = V Or Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10) _
And (Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8)) Then i = i - 1: GoTo 1
If v2 = i And v5 <> "" And Not ((Dat(i, 6) = X Or Dat(i, 6) = y Or Dat(i, 6) = Z Or Dat(i, 6) = z3 Or Dat(i, 6) = an7 Or Dat(i, 6) = an8 _
Or Dat(i, 6) = an9 Or Dat(i, 6) = w Or Dat(i, 6) = V)) Then i = i - 1: GoTo 1
If Difficult = "" And v2 = i And v5 <> "" And Not Dat(i, 1) = v5 Then i = i - 1: GoTo 1
'If base1 = V Or base1 = w Or base1 = X Or base1 = y And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
'And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8) _
'And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And balise < 5 And balise2 = "" And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
'----------------------------
If Difficult = "" And balise < 5 And balise2 < 6 And balise3 = "" And v2 = i And Not ((Dat(i, 5) = V Or Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And v2 = i And balise2 < 6 And base2 <> "" And Not Dat(i, 2) = base2 Then i = i - 1: GoTo 1
'------------------------------------------
'----------------------------
If Difficult = "" And balise < 5 And balise2 < 9 And balise2 > 5 And balise3 = "" And v2 = i And Not ((Dat(i, 5) = w Or Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8) _
And (Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8 Or Dat(i, 2) = an9 Or Dat(i, 2) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And balise < 5 And balise2 < 9 And balise2 > 5 And balise3 = "" And v2 = i And Not Dat(i, 4) = base2 Then i = i - 1: GoTo 1
'------------------------------------------
If Difficult = "" And balise < 5 And balise2 < 6 And balise3 < 5 And v2 = i And Not ((Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10)) Then i = i - 1: GoTo 1
If Difficult = "" And balise < 5 And balise2 < 6 And balise3 < 5 And v2 = i And base2 <> "" And Not Dat(i, 2) = base2 And Not Dat(i, 5) = base3 Then i = i - 1: GoTo 1
If Difficult = "" And balise > 4 And balise2 = "" And balise3 = "" And v2 = i And Not ((Dat(i, 3) = an10 Or Dat(i, 3) = an11 Or Dat(i, 3) = an12 Or Dat(i, 3) = an13 Or Dat(i, 3) = an14 Or Dat(i, 3) = an15 Or Dat(i, 3) = an16) _
And (Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8 Or Dat(i, 4) = an9 Or Dat(i, 4) = an10) _
And (Dat(i, 2) = X Or Dat(i, 2) = y Or Dat(i, 2) = Z Or Dat(i, 2) = z3 Or Dat(i, 2) = an7 Or Dat(i, 2) = an8 Or Dat(i, 2) = an9) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1
'------------------------------------------
'----------------------------------------------------------------------------------------------------------------------------------------------------
' ------------------- si facile ou super facile 1 BALISE
If Difficult = "Facile" And v2 = i And balise < 3 And balise2 = "" And v5 <> "" And Not (Dat(i, 2) = v5 Or Dat(i, 1) = base1) Then i = i - 1: GoTo 1
If Difficult = "Facile" And balise < 3 And balise2 = "" And v2 = i And Not _
((Dat(i, 4) = V Or Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 3) = w Or Dat(i, 3) = X Or Dat(i, 3) = y Or Dat(i, 3) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1
If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 2 And balise < 5 And v5 <> "" And Not Dat(i, 3) = v5 Then i = i - 1: GoTo 1
If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 2 And balise < 5 And balise2 = "" And v2 = i And Not _
((Dat(i, 4) = V Or Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1
If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 4 And v5 <> "" And Not Dat(i, 4) = v5 Then i = i - 1: GoTo 1
If Difficult = "Facile" And v2 = i And balise2 = "" And balise > 4 And balise2 = "" And v2 = i And Not _
((Dat(i, 3) = V Or Dat(i, 3) = w Or Dat(i, 3) = X Or Dat(i, 3) = y Or Dat(i, 3) = Z Or Dat(i, 3) = z3) _
And (Dat(i, 1) = w Or Dat(i, 1) = X Or Dat(i, 1) = y Or Dat(i, 1) = Z Or Dat(i, 1) = z3 Or Dat(i, 1) = V) _
And (Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = V) _
And (Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10 Or Dat(i, 5) = an11)) Then i = i - 1: GoTo 1
' ------------------- si facile ou super facile 2 BALISES
'If base1 = V Or base1 = w Or base1 = X Or base1 = y And v2 = i And Not ((Dat(i, 2) = V Or Dat(i, 2) = w Or Dat(i, 2) = X Or Dat(i, 2) = y) _
'And (Dat(i, 4) = w Or Dat(i, 4) = X Or Dat(i, 4) = y Or Dat(i, 4) = Z Or Dat(i, 4) = z3 Or Dat(i, 4) = an7 Or Dat(i, 4) = an8) _
'And (Dat(i, 5) = X Or Dat(i, 5) = y Or Dat(i, 5) = Z Or Dat(i, 5) = z3 Or Dat(i, 5) = an7 Or Dat(i, 5) = an8 Or Dat(i, 5) = an9 Or Dat(i, 5) = an10)) Then i = i - 1: GoTo 1
' le cheval de base
' If base1 <> "" And v2 = i And Not (Dat(i, 1) = base1 Or Dat(i, 2) = base1 Or Dat(i, 3) = base1 Or Dat(i, 4) = base1 Or Dat(i, 5) = base1) Then i = i - 1: GoTo 1
' chevaux entrés sur la ligne 6 à partir de AF donne l'ordre des combinaisons
1 Next i
Range("A1").Resize(200, 6).Value = Dat
Application.Calculation = xlCalculationAutomatic
End Sub
Private Sub TIRAGE_Click()
toto
End Sub