Random Number Generator

kev8279

New Member
Joined
Oct 12, 2009
Messages
29
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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?

Disable AdBlock

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
Back
Top