Citation :
Sub tirage() Dim i As Integer, nbCadeaux As Integer, num_gagnant As Integer Dim collec As Collection nbCadeaux = 5 With ThisWorkbook.Worksheets("Tirage" ) Set collec = New Collection For i = 1 To .Cells(1, 1).End(xlDown).Row collec.Add Item:=i, Key:=CStr(.Cells(i, 1).Value) Next i .Columns(2).ClearContents Randomize For i = 1 To nbCadeaux num_gagnant = Int((Rnd * collec.Count) + 1) .Cells(collec.Item(num_gagnant), 2).Value = "Cadeau " & CStr(i) 'get winner line from item i collec.Remove (.Cells(collec.Item(num_gagnant), 1).Value) 'remove winner from collec with name as key Next i Set collec = Nothing End With End Sub
|