Hi all,
I'm trying to write/adapt some VBA code that can pick 3 randomly pick names from a list. The must not pick the same name more than once.
I have a master list of names in column A. I have used another piece of code to pick a subset from this column (based on a separate criteria). This subset of names appears in column I. I would like to pick 3 names randomly from the subset of names in column I and output them to column J.
I have adapted the following code:
The problem I'm having is that it's currently picking up the master list of values in column A and not the subset from column I. Can anyone work out what I've missed in the code?
Many thanks,
Chris
I'm trying to write/adapt some VBA code that can pick 3 randomly pick names from a list. The must not pick the same name more than once.
I have a master list of names in column A. I have used another piece of code to pick a subset from this column (based on a separate criteria). This subset of names appears in column I. I would like to pick 3 names randomly from the subset of names in column I and output them to column J.
I have adapted the following code:
Code:
Option Explicit
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 = 3
CellsOut = 1
ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("I:I")) ' Find how many names in the list
i = 1
Do While i <= HowMany
RandomNo:
RandomNumber = Application.RandBetween(2, NoOfNames + 1)
'Check to see if the name has already been picked
For ArI = LBound(Names) To UBound(Names)
If Names(ArI) = Cells(RandomNumber, 1).Value Then
GoTo RandomNo
End If
Next ArI
Names(i) = 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)
Cells(CellsOut, 10) = Names(ArI)
CellsOut = CellsOut + 1
Next ArI
Application.ScreenUpdating = True
End Sub
The problem I'm having is that it's currently picking up the master list of values in column A and not the subset from column I. Can anyone work out what I've missed in the code?
Many thanks,
Chris