I need to create a unique random number from 1-15 in columns D1 thru D15.
There can be no duplicates. It should be a different set of numbers each
time the spreadsheet is loaded. Can someone show me an easy way to
accomplish this?

Don D.

See JE McGimpsey's site.

http://www.mcgimpsey.com/excel/udfs/randint.html


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 15:35:53 -0500, "Don" wrote:

I need to create a unique random number from 1-15 in columns D1 thru D15.
There can be no duplicates. It should be a different set of numbers each
time the spreadsheet is loaded. Can someone show me an easy way to
accomplish this?

Don D.

"Don" wrote
I need to create a unique random number from
1-15 in columns D1 thru D15. There can be no
duplicates.

Ostensibly, you could put the formula =RAND() into 15 helper cells, e.g.
X1:X15. Then put =RANK(X1,$X$1:$X$15) into D1 and copy down through D15.

The problem with that is: the set of random integers in D1:D15 will change
every time you edit or otherwise recalculate any cell in any worksheet in
the workbook(!). This is because RAND() is volatile.

A workaround is to replace =RAND() with =myrand() after adding the following
UDF:


Function myrand(Optional rng As Range)
Static cnt As Long
If cnt = 0 Then Randomize: cnt = 1
myrand = Rnd()
End Function


Then D1:D15 is modified only when the entire workbook is recalculated, e.g.
using ctrl+alt+F9.

Alternatively, if you use =myrand(A1), where is A1 is any cell, then D1:D15
will change each time that A1 is modified.

You can use a range reference instead of a single cell reference. For
example, =myrand(A1:A6) is recalculated every time any of the cells in A1:A6
is modified.


It should be a different set of numbers each
time the spreadsheet is loaded.

One way to do exactly that when using the myrand() UDF is to have the
following Workbook_Open procedu


Private Sub Workbook_Open()
With Application
.ScreenUpdating = False
calcsave = .Calculation
itersave = .Iteration
.Iteration = False
.Calculation = xlCalculationManual
End With
On Error GoTo done

' CHANGE THE FOLLOWING AS NEEDED
Worksheets("Sheet1").Range("X1:X15").Calculate

done:
On Error GoTo 0
With Application
.Iteration = itersave
.Calculation = calcsave
.ScreenUpdating = True
End With
End Sub


That will recalculate X1:X15 and any dependent cells when the Excel file is
opened.


If you are not familiar with event procedures and UDFs, do the following:

1. In the Excel worksheet, press alt+F11 to open the VBA editor (VBE).

2. In the VBE, click on Insert Module to open the editor pane.

3. Copy and paste the myrand() UDF into the editor pane.

4. Double-click ThisWorkbook in the project pane.

5. Copy and paste the Workbook_Open procedure into the editor pane. Change
the Worksheets.Range.Calculate line as needed.

Joe,

I used your first example and it is just what I was looking for. I was
using it to print a list with several names and randomize the order in which
they were listed. I am not doing any further calculations so it works just
fine. I will modify it to allow for the easy addition of new names to the
list if necessary. Thank you very much.

Don D.

"JoeU2004" wrote in message
...
"Don" wrote
I need to create a unique random number from
1-15 in columns D1 thru D15. There can be no
duplicates.

Ostensibly, you could put the formula =RAND() into 15 helper cells, e.g.
X1:X15. Then put =RANK(X1,$X$1:$X$15) into D1 and copy down through D15.

The problem with that is: the set of random integers in D1:D15 will
change every time you edit or otherwise recalculate any cell in any
worksheet in the workbook(!). This is because RAND() is volatile.

A workaround is to replace =RAND() with =myrand() after adding the
following UDF:


Function myrand(Optional rng As Range)
Static cnt As Long
If cnt = 0 Then Randomize: cnt = 1
myrand = Rnd()
End Function


Then D1:D15 is modified only when the entire workbook is recalculated,
e.g. using ctrl+alt+F9.

Alternatively, if you use =myrand(A1), where is A1 is any cell, then
D1:D15 will change each time that A1 is modified.

You can use a range reference instead of a single cell reference. For
example, =myrand(A1:A6) is recalculated every time any of the cells in
A1:A6 is modified.


It should be a different set of numbers each
time the spreadsheet is loaded.

One way to do exactly that when using the myrand() UDF is to have the
following Workbook_Open procedu


Private Sub Workbook_Open()
With Application
.ScreenUpdating = False
calcsave = .Calculation
itersave = .Iteration
.Iteration = False
.Calculation = xlCalculationManual
End With
On Error GoTo done

' CHANGE THE FOLLOWING AS NEEDED
Worksheets("Sheet1").Range("X1:X15").Calculate

done:
On Error GoTo 0
With Application
.Iteration = itersave
.Calculation = calcsave
.ScreenUpdating = True
End With
End Sub


That will recalculate X1:X15 and any dependent cells when the Excel file
is opened.


If you are not familiar with event procedures and UDFs, do the following:

1. In the Excel worksheet, press alt+F11 to open the VBA editor (VBE).

2. In the VBE, click on Insert Module to open the editor pane.

3. Copy and paste the myrand() UDF into the editor pane.

4. Double-click ThisWorkbook in the project pane.

5. Copy and paste the Workbook_Open procedure into the editor pane.
Change the Worksheets.Range.Calculate line as needed.