# Picking numbers randomly from an existing list

#### scouse

##### Board Regular
Greetings,

i have been given 10 case numbers of which 4 need to picked randomly to ensure no bias. How can this be done through a button in excel?

The above is my main concern - but if possible.....
I am looking to make a button which will pick a number and display it, say cell A1 and then, the next time the button is hit, into, say, cell A2 (just so the button can be hit 4 times and the results shown)

Scouse

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Where are the 10 numbers located?

Are they on a worksheet?

I havent done anything yet - all i have been given is a list of the 10 numbers. I was just assuming that i would put them into a worksheet and then somehow randomly select them from the column that they are in,

put numbers in column A and then run this.

Code:
``````Sub test_code()
ncell = 4
lr = [a1].CurrentRegion.Rows.Count

Columns(1).Font.ColorIndex = 1
Columns(1).Font.Bold = False

ReDim test(lr)
ReDim myarray(ncell)  ' this is number of cells to highlight
For r = 1 To lr  ' put cells values in array for now
test(r) = Cells(r, 1)
Next r

For iter = 1 To ncell   ' scan down the cells, pick largest number, remember the row number
mymin = 0
For r = 2 To lr
If Cells(r, 1) > mymin Then
mymin = Cells(r, 1)
myminrow = r
End If
Next r

myarray(iter) = myminrow    ' put row number(s) into myarray
Cells(myminrow, 1) = 0  ' set cell to zero so it's not picked again on next iteration

Next iter

For r = 1 To lr  ' put values back into cells
Cells(r, 1) = test(r)
For x = 1 To ncell
If r = myarray(x) Then
Cells(r, 1).Font.Bold = True  ' if the row was picked before, highlight it in bold
Cells(r, 1).Font.ColorIndex = 3
End If
Next x
Next r
[a1].Select

End Sub``````

If the cases where in range A1:A10 something like this might work:
Code:
``````Sub Get4Random()
Dim X As Long
Dim I As Long
I = 1
Do

X = CLng(Rnd * 10) + 1

If Range("A" & X) <> "" Then
Range("A" & X).Copy Range("B" & I)
Range("A" & X).ClearContents
I = I + 1
End If
Loop Until I = 4

End Sub``````

Thanks to you both - i will give them a go!
Would I attach these to a button?

Norie,
That works perfectly for what i need it for at the moment.
I am using your code attached to a button and it cuts out 4 numbers and puts them into col B. Is there anyway i can reset it once I have used it. At present if i use it, the second time i use it it puts four numbers on top of the others.
I have an idea that if i set it to, say, one random number it could have a wider use. e.g. ppl could click on it 9 or 10 times and get a random list in col B one from B1, B2....Bn

thanks again much appreciated,

Scouse

Scouse

I'm not 100% sure what you mean.

The following code should leave the list in column A intact:
Code:
``````Sub Get4Random()
Dim X As Long
Dim I As Long
Dim arrRange

arrRange = Range("A1:A10")
I = 1
Do

X = CLng(Rnd * 10) + 1

If arrRange(X, 1) <> "" Then

Range("B" & I) = arrRange(X, 1)
arrRange(X, 1) = ""
I = I + 1
End If
Loop Until I = 4

End Sub``````

Replies
0
Views
87
Replies
3
Views
554
Replies
4
Views
276
Replies
2
Views
488
Replies
0
Views
149

1,203,677
Messages
6,056,688
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back