Option Explicit
Sub GO()
Dim TEnt(), NMax&, N&, TSor(1 To 21, 1 To 5), L4Max&, L&, C&, CMax&
TEnt = [B7].Resize([B1000000].End(xlUp).Row - 6).Value
NMax = UBound(TEnt, 1)
L4Max = NMax \ 4 - NMax Mod 4
CMax = 4
For N = 1 To NMax
C = C Mod CMax + 1
If C = 1 Then
If L = L4Max Then CMax = 5
L = L + 1: End If
TSor(L, C) = TEnt(N, 1)
Next N
[J4:N24].Value = TSor
End Sub
Sub test()
Dim c As New Collection, xy, i As Integer, a As String
For Each r In Range(Range("B7"), Cells(Cells.Rows.Count, "B").End(xlUp))
c.Add r.Value
Next
Randomize ' Initialize random-number generator.
[J4: N24] = ""
For Each r In [J4: M24]
i = Int((c.Count * Rnd) + 1) 'random-number generator'
' r.Select
r.Value = c(i)
c.Remove (i)
a = r.Address
If c.Count = 0 Then Exit For
Next
i = 0
xy = Split(a, "$")
While xy(1) < "M" And xy(1) > "I"
i = i + 1
Cells(xy(2) - i, "n") = Range(a)
Range(a).Value = ""
a = Range(a).Offset(0, -1).Address
xy = Split(a, "$")
Wend
End Sub
Sub Tirage()
Dim TEnt(), L&, TAl&(), TSor(), NMax&, N&
TEnt = [V7].Resize([V1000000].End(xlUp).Row - 6, 2).Value
NMax = UBound(TEnt, 1)
InitListeAl TAl, NMax
ReDim TSor(1 To NMax, 1 To 3)
For L = 1 To NMax
N = TAl(L)
TSor(L, 1) = N
TSor(L, 2) = TEnt(N, 1)
TSor(L, 3) = TEnt(N, 2)
Next L
[B:D].ClearContents
[B7].Resize(NMax, 3).Value = TSor
End Sub
Sub InitListeAl(TAl() As Long, Optional ByVal NMax As Long, Optional ByVal Graine As Double)
Rem. ——— Garnit un tableau à une dimension base 1 de numéros sans doublon ou change aléatoirement l'ordre
' des numéros y étant déjà portés.
' Arguments :
' TAl : Le tableau à traiter.
' NMax: Numéro maxi. Si spécifié, le tableau est redimensionné TAl(1 To NMax), puis garni de numéros de 1 à NMax.
' Graine: Base de départ de la série. Si omis la série sera différente à chaque exécution.
Dim P As Long, R As Long, X As Long
If NMax >= 0 Then
ReDim TAl(1 To NMax): For P = 1 To NMax: TAl(P) = P: Next P
Else: NMax = UBound(TAl): End If
If Graine <= 0 Then Randomize Else Rnd -1: Randomize Graine
For P = NMax To 2 Step -1
R = Int(Rnd * P) + 1: X = TAl(R): TAl(R) = TAl(P): TAl(P) = X
Next P
End Sub
For Each r In Range(Range("U7"), Cells(Cells.Rows.Count, "V").End(xlUp).Offset(, -1))
c.Add r.Value
Next
Sub test()
Dim c As New Collection, xy, i As Integer, a As String
For Each r In Range(Range("U7"), Cells(Cells.Rows.Count, "V").End(xlUp).Offset(, -1))
c.Add r.Value
Next
Debug.Print (c.Count - (c.Count Mod 5)) / 5
Randomize ' Initialize random-number generator.
[J4: N24] = ""
For Each r In [J4: M24]
i = Int((c.Count * Rnd) + 1)
r.Select
r.Value = c(i)
c.Remove (i)
a = r.Address
If c.Count = 0 Then Exit For
Next
i = 0
xy = Split(a, "$")
While xy(1) < "M" And xy(1) > "I"
i = i + 1
Cells(xy(2) - i, "n") = Range(a)
Range(a).Value = ""
a = Range(a).Offset(0, -1).Address
xy = Split(a, "$")
Wend
End Sub