Sub tirage()
Dim dl1 As Long ' dernière ligne
Dim nomfeuille1 As String
Dim val1 As Integer
Dim col As New Collection
nomfeuille1 = ActiveSheet.Name
Randomize
Dim a As String
With Sheets(nomfeuille1)
dl1 = .Cells(Rows.Count, 2).End(xlUp).Row
nbequ = .Range("b" & dl1)
For Each cell In .Range("f6:f" & dl1)
Do
erreur = False
val1 = Int((nbequ * Rnd))
On Error GoTo suite
' vérification si même club
If cell.Offset(0, -3) = cell.Offset(val1 + 5 - cell.Row, -3) Then
a = cell.Row ' ligne pour tests
Else
col.Add val1, CStr(val1)
If val1 = 0 Then val1 = 100
If erreur = False Then Exit Do
End If
Loop
cell.Value = val1
Next cell
End With
Exit Sub
suite:
erreur = True
Resume Next
End Sub