# Random Number Generator

#### kev8279

##### New Member
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?

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Special-K99

##### Well-known Member
VBA...

Put the numbers 1 to 40 in an array

Select your first random number 1-40, and retrieve the value at that position in the array
Now shift the array up from the random number you picked till the end of the array (there are now 39 numbers in the array)
Select your first random number 1-39, and retrieve the value at that position in the array
Now shift the array up from the random number you picked till the end of the array (there are now 38 numbers in the array)
Select your first random number 1-38, and retrieve the value at that position in the array
etc

Repeat 40 times or whenever you want to stop

#### bertie

##### Well-known Member
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

#### Akashwani

##### Well-known Member
Hi,

An array formula taken from here.....

How to create a list of random unique numbers in excel | Get Digital Help - Microsoft Excel resource

Excel Workbook
ABCDEF
1UniqueCheck1Check2Check3
2274011
3353911
413811
5403711
6203611
7113511
8323411
9163311
10333211
11283111
1253011
13192911
14302811
15132711
16382611
17122511
18262411
19312311
2062211
21242111
2242011
23291911
24341811
25361711
26371611
27141511
2821411
29391311
30151211
31211111
3281011
333911
3422811
3510711
3627611
3725511
3823411
3917311
407211
419111
42
Sheet1

I've added the other formulas just for a check to ensure there were no duplicates.

I hope that helps.

Ak

#### kev8279

##### New Member
Hi, thanks for this. What I'd like to do with this is have it in a spreadsheet so that when I click a button I get the next number in the array, is that possible?

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

#### dmt32

##### Well-known Member
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?

Have a look here Daily Dose of Excel » Blog Archive » Bingo
There is a sample workbook you can download which may be close to doing what you want.

Dave

Replies
41
Views
1K
Replies
2
Views
276
Replies
6
Views
321
Replies
6
Views
126
Replies
2
Views
141

1,195,849
Messages
6,011,955
Members
441,657
Latest member
Diupsy

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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