Hi all,
I was very helpfully given these 2 UDFs to create a non-repeating cycl
of numbers between the numbers entered (eg: 1 to 14 etc).
The "RandInt" function below successfully creates a list of rando
integers, however the numbers are repeated randomly.

What I would really need is a UDF that creates a non-replicating lis
which cycled thru 14 numbers. ie: if the number 1 comes out first, the
you will get (for example) 5,3,7,8,2,4,9,12,6,10,13,11,14 before 1 come
out again.
Another user gave me the 2nd function "cycrnd" which should sort ou
this problem, but I cant see how it works.

Any and all help gratefully received.
Thanks
Scott.

Public Function RandInt( _
Optional ByVal nStart As Long = 1&, _
Optional ByVal nEnd As Long = -2147483647) As Variant
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nTemp As Long
Dim nRand As Long
Dim i As Long
Dim j As Long
Application.Volatile
If TypeName(Application.Caller) < "Range" Then Exit Function
With Application.Caller
ReDim vResult(1 To .Rows.Count, 1 To .Columns.Count)
nCount = .Count
If nEnd < nStart Then nEnd = nStart + nCount - 1
If nCount nEnd - nStart + 1 Then
RandInt = CVErr(xlErrNum)
Exit Function
ElseIf nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
Exit Function
End If
End With
ReDim vArr(0 To nEnd - nStart)
For i = 0 To UBound(vArr)
vArr(i) = i + nStart
Next i
For i = UBound(vArr) To 1 Step -1
nRand = Int(Rnd() * (i + 1))
nTemp = vArr(nRand)
vArr(nRand) = vArr(i)
vArr(i) = nTemp
Next i
nCount = 0
For i = 1 To UBound(vResult, 1)
For j = 1 To UBound(vResult, 2)
vResult(i, j) = vArr(nCount)
nCount = nCount + 1
Next j
Next i
RandInt = vResult
End Function


Function cycrnd() As Long
Static a As Variant, n As Long
Application.Volatile 'necessary for this

If IsEmpty(a) Then
a = RandInt(1, 16)
n = LBound(a)

Else
If n < UBound(a) Then n = n + 1 Else n = LBound(a)

End If

cycrnd = a(n)

End Functio

--
scottwilson
-----------------------------------------------------------------------
scottwilsonx's Profile: http://www.excelforum.com/member.php...fo&userid=1112
View this thread: http://www.excelforum.com/showthread.php?threadid=27523

Another approach, maybe not too random but simple (and
correct enough, I hope):

A1: 1003
B1: 201 (has to be relative prime to 14!)
B2: =14-MOD($A$1+$A2*B$1,14)
Now write 1, 2, 3, 4, 5, 6, ... into cells A2, A3, ... and
copy formula of cell B2 down.

HTH,
sulprobil