Non updatable Unique Random Number
Hello Ian,
If you need them only once you can create the unique random integers
with my function UniqRandInt, then select them, push CTLR + c, then ALT
+ e, s, v (copy and paste by values).
Or - if you need this functionality more often - you can:
1. Define a named range "MyCount" for a cell where you enter how many
numbers you want to get. Enter 10 into E1, for example, select E1 and
define that range with Insert/Name/Define.
2. Define a named range "MyRange" for a cell where you specify the max
random number you want to get. Take E2 and enter 20, for example (has
to be = 10!).
3. Define a named range "MyTarget" for a range of cells where you want
the output to be written to. Select E4:E13, for example.
4. Then put my function VBUniqRandInt into a module together with this
sub:
Sub Constant_UniqRandInts()
Dim lr As Long
Dim lc As Long
lr = Range("MyRange")
lc = Range("MyCount")
If Range("MyTarget").Columns.Count 1 Then
Range("MyTarget") = VBUniqRandInt(lc, lr)
Else
Range("MyTarget") =
Application.WorksheetFunction.Transpose(VBUniqRand Int(lc, lr))
End If
End Sub
5. Insert a Push Button into your spreadsheet and connect it to the sub
Constant_UniqRandInts
6. Fire off that button. The random numbers won't change on hitting F9
- just when you push the button.
HTH,
Bernd
|