Don said:
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
15 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
15 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
15
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 procedure:
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.
