Jason:
Follow this thread and see if it is any help:
14932.html
Kev
Jason
What follows is due Mark W in response to a similar question at this bord. I can't get at the URL of that thread (probably already archived). Here is the whole exchange, slightly edited:
-------------------------------------
Posted by Jim L on January 30, 2001 at 10:17:23:
I'd like to generate a series of 36 random
numbers without duplicates. The 36 random
numbers begin at #1 and end at #36.
Posted by Mark W. on January 30, 2001 at 11:33:01:
In Reply to: Random Number without duplicates posted by Jim L on January 30, 2001 at 10:17:23:
1. Enter the formula, =RAND(), into cells A1:A36.
2. Enter the formula, =RANK(A1,$A$1:$A$36), into
cell B1, and copy down.
-----------------------------------------------
Hi Jason
Here is some code I wrote for creating random numbers. To use it push Alt+F11 and go to Insert>Module then paste in the code. Push Alt+Q to return to Excel and Save. Now push Alt+F8 and click "RandomNumberGenerator" click Options and assign a shortcut key. In this particular case the code will create 36 random numbers between 1 and 36.
I am very close to completing an add-in that will include a "random number generator" that will allow you to nominate the Lowest number, Highest number and the Number of random numbers. Let me know if you are interested.
Sub RandomNumberGenerator()
'Creates a list of random numbers _
between 1 and 36 in range A1:F6
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Range("A1:F6").Clear
Randomize ' Initialize random-number generator.
For Col = 1 To 6 'Set the Column numbers
For Rw = 1 To 6 'Set the Row numbers
'
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:F6"), Cells(Rw, Col)) = 1
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Loop
Next Rw
Next Col
End Sub
Dave
OzGrid Business Applications
Great tip! Simple and effective!