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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,410
Messages
5,528,613
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top