Sub Tirage()
Dim tableau As Range, d As Object, n%, tablo As Range, i%, j%, col%, x$, y$
Set tableau = [B5:Y9,B13:Y17,B21:Y25]
Set d = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'évite le recalcul des formules NB.SI
Randomize
For n = 1 To 3
Set tablo = tableau.Areas(n)
1 tablo.ClearContents 'RAZ
d.RemoveAll
For i = 1 To 5
'---placement aléatoire des 1000---
For j = 1 To tablo(i, 30)
2 col = Application.RandBetween(1, 24)
If tablo(i, col) = "" Then tablo(i, col) = 1000 Else GoTo 2
Next j
'---placement aléatoire des 900---
For j = 1 To tablo(i, 31)
3 col = Application.RandBetween(1, 24)
If tablo(i, col) = "" Then tablo(i, col) = 900 Else GoTo 3
Next j
'---placement aléatoire des 2000---
For j = 1 To tablo(i, 32)
4 col = Application.RandBetween(1, 24)
If tablo(i, col) = "" Then tablo(i, col) = 2000 Else GoTo 4
Next j, i
'---évite les doublons de colonnes---
For col = 1 To 24
x = tablo(1, col) & tablo(2, col) & tablo(3, col) & tablo(4, col) & tablo(5, col)
If d.exists(x) Then GoTo 1 Else d(x) = ""
If n > 1 Then
y = tableau.Areas(n - 1)(1, col) & tableau.Areas(n - 1)(2, col) & tableau.Areas(n - 1)(3, col) & tableau.Areas(n - 1)(4, col) & tableau.Areas(n - 1)(5, col)
If x = y Then GoTo 1
End If
If n = 3 Then
y = tableau.Areas(1)(1, col) & tableau.Areas(1)(2, col) & tableau.Areas(1)(3, col) & tableau.Areas(1)(4, col) & tableau.Areas(1)(5, col)
If x = y Then GoTo 1
End If
Next col
Next n
Application.Calculation = xlCalculationAutomatic
End Sub