Mark O'Brien
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 3,530
OK, to go back to the original post. Here is a bit of VBA that will give a random order for the range 1 to 233. Each number is used only once. I 100% guarantee that there can be no duplicates because I used an old ZX Spectrum card shuffling technique of using an array to hold which numbers have and have not been used. It took about 1 second for the complete code to run and put data in column A. This is a AMD 233 MHz machine I'm using.
<pre>
Public Sub main()
Dim iRandArray() As Integer
Dim i As Integer
RandomOrder 233, iRandArray()
For i = 1 To UBound(iRandArray)
Sheets("Sheet1").Range("A1").Offset(i, 0).Value = iRandArray(i)
Next
End Sub
Private Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer)
Dim iNumArray() As Integer
Dim iRandValue As Integer
ReDim iNumArray(1 To iNumRange, 1 To 2)
ReDim iRandArray(1 To iNumRange)
For i = 1 To iNumRange
iNumArray(i, 1) = i
iNumArray(i, 2) = 1
Next
For i = 1 To iNumRange
Randomize
iRandValue = Int((iNumRange * Rnd) + 1)
If iNumArray(iRandValue, 2) = 0 Then
i = i - 1
Else
iRandArray(i) = iRandValue
iNumArray(iRandValue, 2) = 0
End If
Next
End Sub</pre>
I think that I agree with Jack about Dave's code possibly giving repeats, but he it seems that he reduces the likelyhood of this with the way he's coded it.
HTH
<pre>
Public Sub main()
Dim iRandArray() As Integer
Dim i As Integer
RandomOrder 233, iRandArray()
For i = 1 To UBound(iRandArray)
Sheets("Sheet1").Range("A1").Offset(i, 0).Value = iRandArray(i)
Next
End Sub
Private Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer)
Dim iNumArray() As Integer
Dim iRandValue As Integer
ReDim iNumArray(1 To iNumRange, 1 To 2)
ReDim iRandArray(1 To iNumRange)
For i = 1 To iNumRange
iNumArray(i, 1) = i
iNumArray(i, 2) = 1
Next
For i = 1 To iNumRange
Randomize
iRandValue = Int((iNumRange * Rnd) + 1)
If iNumArray(iRandValue, 2) = 0 Then
i = i - 1
Else
iRandArray(i) = iRandValue
iNumArray(iRandValue, 2) = 0
End If
Next
End Sub</pre>
I think that I agree with Jack about Dave's code possibly giving repeats, but he it seems that he reduces the likelyhood of this with the way he's coded it.
HTH