Sub random()
Dim arrCheck(1 To 99) As Long
Dim arrBingo(1 To 99) As Long
Dim count As Long
Dim i As Long
'loop to populate the bingo array
Do Until count = 99
count = count + 1
'loop to check if number has previously been used
Do
Randomize
i = Int(99 * Rnd + 1)
If arrCheck(i) = 0 Then
arrCheck(i) = i
arrBingo(count) = i
Exit Do
End If
Loop
Loop
Stop
'click View => Locals Window
'expand array variables
End Sub
Excel Workbook | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Unique | Check1 | Check2 | Check3 | ||||
2 | 27 | 40 | 1 | 1 | ||||
3 | 35 | 39 | 1 | 1 | ||||
4 | 1 | 38 | 1 | 1 | ||||
5 | 40 | 37 | 1 | 1 | ||||
6 | 20 | 36 | 1 | 1 | ||||
7 | 11 | 35 | 1 | 1 | ||||
8 | 32 | 34 | 1 | 1 | ||||
9 | 16 | 33 | 1 | 1 | ||||
10 | 33 | 32 | 1 | 1 | ||||
11 | 28 | 31 | 1 | 1 | ||||
12 | 5 | 30 | 1 | 1 | ||||
13 | 19 | 29 | 1 | 1 | ||||
14 | 30 | 28 | 1 | 1 | ||||
15 | 13 | 27 | 1 | 1 | ||||
16 | 38 | 26 | 1 | 1 | ||||
17 | 12 | 25 | 1 | 1 | ||||
18 | 26 | 24 | 1 | 1 | ||||
19 | 31 | 23 | 1 | 1 | ||||
20 | 6 | 22 | 1 | 1 | ||||
21 | 24 | 21 | 1 | 1 | ||||
22 | 4 | 20 | 1 | 1 | ||||
23 | 29 | 19 | 1 | 1 | ||||
24 | 34 | 18 | 1 | 1 | ||||
25 | 36 | 17 | 1 | 1 | ||||
26 | 37 | 16 | 1 | 1 | ||||
27 | 14 | 15 | 1 | 1 | ||||
28 | 2 | 14 | 1 | 1 | ||||
29 | 39 | 13 | 1 | 1 | ||||
30 | 15 | 12 | 1 | 1 | ||||
31 | 21 | 11 | 1 | 1 | ||||
32 | 8 | 10 | 1 | 1 | ||||
33 | 3 | 9 | 1 | 1 | ||||
34 | 22 | 8 | 1 | 1 | ||||
35 | 10 | 7 | 1 | 1 | ||||
36 | 27 | 6 | 1 | 1 | ||||
37 | 25 | 5 | 1 | 1 | ||||
38 | 23 | 4 | 1 | 1 | ||||
39 | 17 | 3 | 1 | 1 | ||||
40 | 7 | 2 | 1 | 1 | ||||
41 | 9 | 1 | 1 | 1 | ||||
42 | ||||||||
Sheet1 |
Try this in a standard module. When the code stops click View => Locals window and expand the array variables to see how the values are stored.
You now have a random array of unique numbers.
Rich (BB code):Sub random() Dim arrCheck(1 To 99) As Long Dim arrBingo(1 To 99) As Long Dim count As Long Dim i As Long 'loop to populate the bingo array Do Until count = 99 count = count + 1 'loop to check if number has previously been used Do Randomize i = Int(99 * Rnd + 1) If arrCheck(i) = 0 Then arrCheck(i) = i arrBingo(count) = i Exit Do End If Loop Loop Stop 'click View => Locals Window 'expand array variables End Sub
I need some code that picks a random number between 1 and 40 but without repeating a selection, so a little like a bingo machine.
Can anyone help?