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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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