1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| Option Explicit
Const S As Byte = 5
Sub Test()
GenereSerieAleatoireSansDoublons 20, Range("B2")
End Sub
Private Sub GenereSerieAleatoireSansDoublons(ByRef Nb As Long, Dest As Range)
Dim i As Integer, R As Integer, j As Integer, m As Integer, q As Integer, Tb() As Integer
Dim P() As String, L() As String
Dim k As Byte
Dim H
Application.StatusBar = "Recherche combinaisons en cours..."
Nb = S * (Nb \ S)
Dest.Resize(Nb / S, Nb).ClearContents
ReDim Tb(1 To Nb / S, 1 To Nb)
ReDim L(1 To Nb)
ReDim P(1 To Nb / S)
For k = 1 To Nb / S
i = 1
Do
DoEvents
R = NUMERO(Nb, P, L, i, k)
j = 1 + (i - 1) \ S
If R = 0 Then
If k > 1 Then
For q = 1 To Nb / S
H = Split(P(q), ",")
P(q) = ""
For m = 0 To S * (k - 1)
If H(m) <> "" Then P(q) = P(q) & "," & H(m)
Next m
Next q
For m = 1 To Nb
Tb(k, i) = Empty
Next m
L(k) = ""
i = 1
End If
Else
P(j) = P(j) & "," & R
L(k) = L(k) & "," & R
Tb(k, i) = R
i = i + 1
End If
Loop While i <= Nb
Next k
Dest.Resize(Nb / S, Nb).Value = Tb
Application.StatusBar = False
End Sub
Private Function NUMERO(ByVal Nb As Long, ByVal P, ByVal L, ByVal i As Integer, ByVal k As Byte) As Integer
Dim R As Integer
Dim Cp As Long
Randomize
Do
DoEvents
R = Int(Nb * Rnd()) + 1
Cp = Cp + 1
If Cp > 200 Then Exit Function
Loop Until Not DEJAP(P, R, i) And Not DEJAL(L, R, k)
NUMERO = R
End Function
Private Function DEJAP(ByVal vP, ByVal vR As Long, ByVal i As Long) As Boolean
Dim k As Long
k = 1 + (i - 1) \ S
DEJAP = InStr(vP(k) & ",", "," & CStr(vR) & ",")
End Function
Private Function DEJAL(ByVal vL, ByVal vR As Long, ByVal k As Long) As Boolean
DEJAL = InStr(vL(k) & ",", "," & CStr(vR) & ",")
End Function |
Partager