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?
|