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?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,331
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
Joined
Jun 12, 2009
Messages
1,869
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
Joined
Mar 14, 2009
Messages
2,911
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
Joined
Oct 12, 2009
Messages
29
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
 

Forum statistics

Threads
1,086,227
Messages
5,388,571
Members
402,125
Latest member
mtwood83

Some videos you may like

This Week's Hot Topics

Top