ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Return random numbers

The worksheetfunction RAND() will return a decimal value greater than or equal to 0 and less than 1. Here are some examples on how to use this function :

=RAND()*100 Returns a decimal value between 0 and 100
=RAND()*(100-50)+50
Returns a decimal value between 50 and 100
=ROUND(RAND()*100,0) Returns an integer value between 0 and 100

 

Unique random numbers

With the user defined function below you can create a liste of unique random numbers:

Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
' creates an array with NumCount unique long random numbers in 
' the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
    UniqueRandomNumbers = False
    If NumCount < 1 Then Exit Function
    If LLimit > ULimit Then Exit Function
    If NumCount > (ULimit - LLimit + 1) Then Exit Function
    Set RandColl = New Collection
    Randomize
    Do
        On Error Resume Next
        i = CLng(Rnd * (ULimit - LLimit) + LLimit)
        RandColl.Add i, CStr(i)
        On Error GoTo 0
    Loop Until RandColl.Count = NumCount
    ReDim varTemp(1 To NumCount)
    For i = 1 To NumCount
        varTemp(i) = RandColl(i)
    Next i
    Set RandColl = Nothing
    UniqueRandomNumbers = varTemp
    Erase varTemp
End Function

' example use:
Sub TestUniqueRandomNumbers()
Dim varrRandomNumberList As Variant, cl As Range, i As Long
    ' create 50 random numbers between 1 and 100
    varrRandomNumberList = UniqueRandomNumbers(50, 1, 100)
    ' show results in one column
    Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _
        Application.Transpose(varrRandomNumberList)
    ' show results in one row
    Range(Cells(3, 3), Cells(3, 52)).Value = varrRandomNumberList

    ' get 16 random numbers and populate 16 cells
    varrRandomNumberList = UniqueRandomNumbers(16, 1, 100)
    For Each cl In Range("A1:D4")
        i = i + 1
        cl.Formula = varrRandomNumberList(i)
    Next cl
    Set cl = Nothing

End Sub

This function will not work in Excel 5/95.

 

Document last updated 2003-03-18 12:45:07      Printerfriendly version

User comments:
jim from Orland Park.il wrote (2005-08-10 04:10:42 CET):
Random Numbers In Driffrent Cells
Hello,I finally understood what you meant about repeating the code it took me awhile to figure out,this part of the code I didn't understand. 'get 16 random numbers and populate 16 cells
varrRandomNumberList = UniqueRandomNumbers(16, 1, 100)
For Each cl In Range("A1:D4")
i = i + 1
cl.Formula = varrRandomNumberList(i)
Next cl
Set cl = Nothing
.
jim from orland park.il wrote (2005-08-07 06:40:51 CET):
Random Numbers In Driffrent Cells
Hi again, I tried the above example and got a bunch of numbers in diffrent cells that i wanted,I modified it with my #s,rows and columns
but only got it to work in 1 row and 1 column, maybe i'm typing the code in the wrong place i don't know tried every way but the right one
thank's again for your help but i'm only a beginer and will take awhile to figure out,still getting out of range and error 9. bye and thank's again.
Ole P. from Norway wrote (2005-08-06 14:31:19 CET):
Re: Random Numbers In Driffrent Cells
Yes, this is possible.
You will have to repeat the code that retrieves the required number of random numbers and populate the cells you want numbers in...
This can be done in many ways, you'll figure it out if you look at the code example above.
Jim from Orland Park,IL wrote (2005-08-06 06:10:33 CET):
Random Numbers In Driffrent Cells
Hi i'm having trouble with the updated code,it works fine the way you wrote it but when i try and put the numbers,rows and columns that i need i get subscript 9 error,don't understand.The #'s that i need are 0-9 in rows F1 to O1,F2 to O2,F3 to O3and F4 to O4 and in columns A13 to A22,B13 to B22,C13 to C22 and D13 to D22 is this possible.Thank's again.
Ole P. from Norway wrote (2005-08-05 14:40:47 CET):
Re: random numbers in 4 cells at one time
See the updated example above.
Jim from Orland Park,IL wrote (2005-08-05 04:52:39 CET):
Best site I have been on
This is the best site for excel help I have been on thank you so much for your help will recommened to anyone.
Jim from Orland Park,IL wrote (2005-08-05 04:50:16 CET):
random numbers in 4 cells at one time
Hello thank you for your tip it was very helpful, I have one more ?,I need this formula to work on 4 columns and 4 rows with each one having diffrent order of numbers in it is this possible.example: in column a #s 0-9 random and in column b same thing but diffrent order as in column a.same for rows 1 and 2.thank you.
Ole P. from Norway wrote (2005-08-04 10:59:52 CET):
Re: Random Numbers In Driffrent Cells
If you want to display the result in a row you don't need to transpose the result:
Range(Cells(3, 3), Cells(3, 52)).Value = varrRandomNumberList
Jim from Orland Park,IL wrote (2005-08-04 04:43:16 CET):
Random Numbers In Driffrent Cells
The unique random number formula work's fine going from cells A1 to A9 there are no reapet numbers from 1 thru 9,but going from cell A1 to I1 all the numbers are the same.Is there anything i need to add to the formula for this to work horizontal and not just vertical.

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address