I would like to pick random names from columns in excel like this :
-In the first sheet "Inscrp" is where the lists are, and the second sheet "Tirage" is where the results of the picking.
-Column A in the sheet "Tirage" should pick random names from column A in the sheet "Inscrp" and the same for the column B, C , till the number of columns I chose I managed to do this with only the first column and here is the code :
Sub PickNamesAtRandom()
Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes
Application.ScreenUpdating = False
HowMany = 5
CellsOut = 8
ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Worksheets("Inscrp").Range("A3:A100")) - 1 ' Find how many names in the list
i = 1
Do While i <= HowMany
RandomNo:
RandomNumber = Application.RandBetween(3, NoOfNames + 1)
'Check to see if the name has already been picked
For ArI = LBound(Names) To UBound(Names)
If Names(ArI) = Worksheets("Inscrp").Cells(RandomNumber, 1).Value Then
GoTo RandomNo
End If
Next ArI
Names(i) = Worksheets("Inscrp").Cells(RandomNumber, 1).Value ' Assign random name to the array
i = i + 1
Loop
'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)
Worksheets("Tirage").Cells(CellsOut, 1) = Names(ArI)
CellsOut = CellsOut + 1
Next ArI
Application.ScreenUpdating = True
End Sub
