gernerate random numbers 1 to 25

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
I need to generate random numbers from 1 to 25 and store them in an array so that I can then pull test questions from a bank, based on the order of the numbers in the array. I'm using this code to generate my random number.

Code:
randomvalue = CInt(Int((25 - 1 + 1) * Rnd() + 1))
How do I store each number generated in an array, after checking the array to see if it has already been added? The array must have no repeated numbers in it, and must include all the numbers from 1 to 25 in a random order (i.e. 24, 7, 2, 19, 13, 1, 4, ...... )

Tom
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
This is how I do it with a simple set of cells and formulas. You can put any values in A2:A26 and they appear randomized in column E. Press F9 to get a new random set.

Copy column E and Edit > Paste Special > Values to save a set of numbers.

Excel Workbook
ABCDE
1NumbersKeyRandom Order
210.3266467313
320.9441671917
430.3658770818
540.4058769320
650.2677656110
760.2915905314
870.297298875
980.4532592412
1090.877060146
11100.183612077
12110.664738371
13120.273582573
14130.0153944123
15140.232576974
16150.644670878
17160.8961669425
18170.0935670615
19180.1516150611
20190.9899406121
21200.1772557624
22210.7221006422
23220.787304939
24230.3871113616
25240.775289512
26250.5939848919
Sheet1
 

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
Here is the code... The random sequence will be in provided in the A() array.

Code:
Option Explicit
Dim a(25) As Integer

Sub GenRndArray()
    Dim RndVal As Integer
    Dim Idx As Integer
    Dim I As Integer
    
    Dim used(26) As Boolean
    
    Idx = 1
    Do While Idx <= 25
        RndVal = Int(25 - 1 + 1) * Rnd + 1
        If Not used(RndVal) Then
            used(RndVal) = True
            a(Idx) = RndVal
            Idx = Idx + 1
        End If
    Loop
End Sub
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi Tom,

Here's my version which is based on b.downey's clever solution - the main difference being my macro will produce the list in a different order each time it's run:

Code:
Option Explicit
Sub Macro2()
    
    'http://www.mrexcel.com/forum/showthread.php?t=588374

    Dim intRndVal As Integer
    Dim intLoopCounter As Integer, _
        intMinNum As Integer, _
        intMaxNum As Integer
    Dim blnNumberUsed() As Boolean
        
    intMinNum = 1 'Minimum number in draw.  Change to suit.
    intMaxNum = 25 'Maximum number in draw.  Change to suit.
    
    ReDim blnNumberUsed(intMinNum To intMaxNum)
    Randomize 'Without this, the numbers will always be displayed in the same order.
    
    intLoopCounter = 1
    Do While intLoopCounter <= intMaxNum
        intRndVal = Int(intMaxNum + 1 - intMinNum) * Rnd() + intMinNum
        If intRndVal >= intMinNum And intRndVal <= intMaxNum Then 'Ensure the correct number range is isued
            If blnNumberUsed(intRndVal) = False Then
                blnNumberUsed(intRndVal) = True
                MsgBox intRndVal
                intLoopCounter = intLoopCounter + 1
            End If
        End If
    Loop
    
End Sub

HTH

Robert
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,701
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

This function will return a Variant containing an array of 25 non-repeating integers...

Code:
Function Rnd25() As Variant
  Dim X As Long, RandomIndex As Long, Temp As Long, Arr As Variant
  ReDim Arr(1 To 25)
  For X = 1 To 25
    Arr(X) = X
  Next
  For X = 25 To 1 Step -1
    RandomIndex = Int(X * Rnd + 1)
    Temp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(X)
    Arr(X) = Temp
  Next
  Rnd25 = Arr
End Function
Here is a sample macro that demostrates its use...

Code:
Sub TestRnd25()
  Dim Msg As String, MyArr As Variant
  MyArr = Rnd25()
  For X = LBound(MyArr) To UBound(MyArr)
    Msg = Msg & MyArr(X) & vbLf
  Next
  MsgBox Msg
End Sub
 

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
wow... thanks guys, much more than I hoped for. Works great.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,701
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

wow... thanks guys, much more than I hoped for. Works great.
Not sure which routine you settled on, but in case my opening line mislead you, I should have worded it this way...

"This function will return a Variant containing an array of the first 25 integers in a
non-repeating random order (the order will be different each time its called)..."

To that end, I would like to modify the code slightly to ensure the "different order" each time its called after Excel starts up for the first time in a session...

Code:
Function Rnd25() As Variant
  Dim X As Long, RandomIndex As Long, Temp As Long, Arr As Variant
  Static IsRandomized As Boolean
  If Not IsRandomized Then
    Randomize
    IsRandomized = True
  End If
  ReDim Arr(1 To 25)
  For X = 1 To 25
    Arr(X) = X
  Next
  For X = 25 To 1 Step -1
    RandomIndex = Int(X * Rnd + 1)
    Temp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(X)
    Arr(X) = Temp
  Next
  Rnd25 = Arr
End Function
 
Last edited:

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
Thanks Rick,

Your first version seemed to work fine, but I've changed it out for your newer version and it also works without issues. My project is a PowerPoint education tool that generates a quiz/game by randomly arranging vocabulary cards for a matching quiz. I was only able to get the cards to generate in the same order each time, which isn't very challenging, so your code solved that. Thanks again.

Tom
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,701
Office Version
  1. 2010
Platform
  1. Windows
Your first version seemed to work fine, but I've changed it out for your newer version and it also works without issues.
I didn't test it, and I haven't used random number stuff in quite a long time, but my recollection is that each time you start Excel for the first time in a session, the same Rnd function calls the same set of random numbers. The modification I made in my second function guarantees that new random numbers are generated each time the function is called no matter if it is in the same session, or a freshly started session, of Excel.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

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
Top