Link to home
Create AccountLog in
Avatar of kacor
kacorFlag for Hungary

asked on

Fill a range with random numbers

Hi Experts,

I want to fill a range (for example A2 : A10) with integer numbers (for example from 2 up to 10) which may occur only once in this range.
Any suggestions appreciated.

Thanks kacor
Avatar of [ fanpages ]
[ fanpages ]

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of [ fanpages ]
[ fanpages ]

PS. Is the extent of the range always going to be a relatively small quantity (&, hence, will the random sample of numbers always be a low quantity also)?

There are a few approaches to take, & the solution provided (if needed, after you have read the contents of the previous two questions) could be tailored to your future/ongoing requirements.

For example, if you needed 10,000 unique random numbers, then the method used to generate these could be different to a method if you only required nine.
Actually, changing one of my previous solutions was a quick process.

Please find attached a workbook containing the following code:

Option Explicit
Public Sub Select_Unrepeated_Random_Numbers(ByRef lngArray() As Long, _
                                            Optional ByVal lngMinimum As Long = 0&, _
                                            Optional ByVal lngMaximum As Long = 0&)

' --------------------------------------------------------------------------------------------------------------
' [ ]
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
' ID:               Q_28129926
' Question Title:   Fill a range with random numbers
' Question Asker:   kacor
' Question Dated:   2013-05-16 at 13:13:07
' Expert Comment:   fanpages
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited / N.Lee [ ]
' Based on...
' [ ]
' Question Channel: Home \ All Topics \ Programming \ Languages \ Visual Basic
' ID:               Q_21743518
' Question Title:   Random generator not repeating numbers. How to stop it?
' Copyright:        (c) 2006 Clearlogic Concepts (UK) Limited / N.Lee [ ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnWend                                           As Boolean
  Dim lngLoop                                           As Long
  Dim lngIndex                                          As Long
  Dim lngValue                                          As Long
  Dim objDictionary                                     As Object
  Dim vntKeys                                           As Variant
  On Error Resume Next
  If lngMinimum = 0& And lngMaximum = 0& Then
     lngMinimum = LBound(lngArray)
     lngMaximum = UBound(lngArray)
  End If
  If lngMaximum - lngMinimum < UBound(lngArray) - LBound(lngArray) Then
     Exit Sub
  End If
  Erase lngArray()
  Set objDictionary = CreateObject("Scripting.Dictionary")
  While Not (blnWend)
     lngValue = CLng(Int(Rnd() * (lngMaximum + 1& - lngMinimum))) + lngMinimum
     If Not (objDictionary.Exists(lngValue)) Then
        objDictionary.Add lngValue, objDictionary.Count + 1&
        blnWend = (objDictionary.Count = UBound(lngArray) + 1& - LBound(lngArray))
     End If
  vntKeys = objDictionary.Keys
  lngIndex = -1&
  For lngLoop = LBound(lngArray) To UBound(lngArray)
      lngIndex = lngIndex + 1&
      lngArray(lngLoop) = vntKeys(lngIndex)
  Next lngLoop
  Set objDictionary = Nothing
End Sub
Public Sub Test()

  Dim lngArray(8&)                                      As Long ' 9 Numbers [2..10]
  Dim lngLoop                                           As Long
  Call Select_Unrepeated_Random_Numbers(lngArray, 2&, 10&)
  For lngLoop = LBound(lngArray) To UBound(lngArray)
      Cells(lngLoop + 2&, 1) = lngArray(lngLoop)
  Next lngLoop
End Sub

Open in new window

Please run the "Test" subroutine to create random numbers in the range [A2:A10].


Without using VBA, you can generate a non-repeating list of random integers using this array-entered formula:

Integers is a named range containing the numbers 2 through 10 (or whatever) that you wish to pick from
RandomNumbers is an equivalent list of random numbers

To array-enter a formula:
1)  In this case, select all the cells that will be using the formula
2)  Paste the formula in the formula bar (or click in the formula bar if the formula is already there)
3.  Hold the Control and Shift keys down, then hit Enter
4.  Release all three keys
Excel should respond by adding curly braces { } surrounding your formula.

To get a new randomized arrangement of integers, hit F9 (or enter data) to force a recalculation of the random numbers.
You could remove the dependency on the Integers named range, Brad, if you changed your (array) formula to:


I am still dubious about using such a technique on, say, 10,000 unique random numbers though :)


with integer numbers (for example from 2 up to 10)
I considered using ROW($2:$10), but the above wording in the question suggested a more general approach would be preferred.

By referring to cells, the formula could even return text. For example, if the Integers range listed out the suits and values for a deck of cards, the formula could deal out one or more hands in a poker simulation.
...By referring to cells, the formula could even return text. For example, if the Integers range listed out the suits and values for a deck of cards, the formula could deal out one or more hands in a poker simulation.

I also toyed with something similar, but thought that simply adding a VLOOKUP (or INDEX/MATCH combination) formula in an adjoining cell could return text instead of numbers, if there was a "lookup table" for the text with an associated number within the random number range.  My initial thought was answers for multiple choice questions (ensuring that at least one of the random responses was the correct answer, of course).

Actually, I do recall reading a thread about producing a deck of cards before:
[ ]

Also, for interest, here is a Previous Asked Question I contributed to:

"How can I return a list of ASELECT values within a range I specify and an average I specify"
[ ]

The workbook that was available for download has long since been removed, but I could attach it within that (or this) thread now, if you think it useful.


Yes. is regurgitating content from Experts-Exchange without getting permission. They aren't the only ones to do that.
Avatar of kacor


Thanks for this ideas!

You are very welcome, kacor.

Did you use one of the solutions in the previous two Question threads?