How can I select random whole nos from 1-16 without repeating?

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

also see Laurent Longre's MRAND function in his MoreFunc.xll at
http://xcell05.free.fr/english/index.html

Jerry

"ilena" wrote:

How can I select random whole nos from 1-16 without repeating?

ilena:

try,

Dim n2&, i&, rr&, rd(), rg As Range
n2 = 16
Set rg = Range("b1")
ReDim rd(1 To n2, 1 To 1)
Randomize
For i = 1 To n2
Do
rr = Int((n2) * Rnd + 1)
Loop While Not IsError(Application.Match(rr, rd, 0))
rd(i, 1) = rr
Next i
With rg.Resize(n2)
.Value = rd
End With

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"ilena" wrote:

How can I select random whole nos from 1-16 without repeating?

for smallish lists use the worksheet. let column A hold the list of numbers
in sequence then in B put =RAND() . Now sort the table by column B and save
the list in A into memory. create an index that walks the array. here's some
simple code...just copy into a module. run 'Initialise' first, the the sheet
type into a cell
=GetNumber()

as I set the function to volatile, each time you hit F9, the value changes
Option Explicit
Public myrand() As Double
Private myrand_index As Long
Sub Inititalise()
generate 1, 16
End Sub
Public Function GetNumber()
Application.Volatile
If myrand_index = 0 Then Exit Function

GetNumber = myrand(myrand_index)
myrand_index = myrand_index + 1

If myrand_index UBound(myrand, 1) Then
myrand_index = 1
End If

End Function
Private Sub generate(minimim As Integer, maximum As Integer)
Dim index As Long
Dim ws As Worksheet
Dim status As Boolean
status = Application.ScreenUpdating
Application.ScreenUpdating = False
Set ws = Worksheets.Add
With ws
For index = 1 To maximum - minimim + 1
.Cells(index, 1) = index + minimim - 1
.Cells(index, 2).Formula = "=rand()"
Next

.Range(.Range("a1"), .Cells(index - 1, 2)).Sort .Range("B1")

ReDim myrand(1 To index - 1)

For index = 1 To index - 1
myrand(index) = .Cells(index, 1)
Next
myrand_index = 1
End With
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = status
End Sub



"ilena" wrote:

How can I select random whole nos from 1-16 without repeating?