
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()*(10050)+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 20030318 12:45:07
Printerfriendly version
jim from Orland Park.il wrote (20050810 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 (20050807 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 (20050806 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 (20050806 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 09 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 (20050805 14:40:47 CET):

Re: random numbers in 4 cells at one time See the updated example above.

Jim from Orland Park,IL wrote (20050805 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 (20050805 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 09 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 (20050804 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 (20050804 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.


