Random Numbers with no repeat

[brianfosterblack]

I have a macro which allows me to allocate a random number between 1 and 100 between cells A3 and DV3. The random number selected is never repeated in row 3. The iteration is currently set to only 1 row but the macro currently allows me to add more rows by changing the iteration (I do not need this feature for the change I need)
I need a macro that does the same but puts the results into Column A row 1 to 100

Can anyone help please? Here is the code - No problem if another simpler solution is available rather than changing this code.

Option Explicit
Option Base 1
Public jj As Long
Public Const iteration = 1

'***********************************************************************
'* Resampling Process *
'***********************************************************************

Sub Resample()
Dim i As Long
Dim hold(100) As Single, Hold2(100) As Single
Randomize

For i = 1 To 100
Hold2(i) = i
Next i
For jj = 1 To iteration
For i = 1 To 100
hold(i) = Rnd
Next i
Call DoubleSort(100, hold, Hold2)
For i = 1 To 100
Cells(jj + 2, i) = Hold2(i)
Next i
Next jj
End Sub

'***********************************************************************
'* Sorting Process - Sort array y based on array x *
'***********************************************************************

Sub DoubleSort(n As Long, x() As Single, y() As Single)
Dim xTemp As Double
Dim yTemp As Double
Dim i As Long
Dim j As Long

For j = 2 To n
xTemp = x(j)
yTemp = y(j)
For i = j - 1 To 1 Step -1
If (x(i) <= xTemp) Then GoTo 10
x(i + 1) = x(i)
y(i + 1) = y(i)
Next i
i = 0
10 x(i + 1) = xTemp
y(i + 1) = yTemp
Next j

End Sub

 

[DanteAmor]

Try this

Sub RandomNumbers()
    Dim n As New Collection, i As Long, ale As Long
    For i = 1 To 100
        n.Add i
    Next
    For i = 1 To 100
        ale = WorksheetFunction.RandBetween(1, n.Count)
        Cells(i, "A") = n(ale)
        n.Remove ale
    Next
    MsgBox "Done"
End Sub

 

[brianfosterblack]

This works perfectly.

Thank you very much

 

[Rick Rothstein]

This works perfectly.

While you will not be able to see or sense the difference for only 100 values, the following macro registers (on my computer at least) as slightly more efficient than the code posted earlier...

Sub RandomNumbers2()
  Dim X As Long, Idx As Long, Tmp As Variant, Arr As Variant
  Arr = [ROW(1:100)]
  For X = 100 To 1 Step -1
    Idx = WorksheetFunction.RandBetween(1, X)
    Tmp = Arr(Idx, 1)
    Arr(Idx, 1) = Arr(X, 1)
    Arr(X, 1) = Tmp
  Next
  [A1:A100] = Arr
End Sub

 

[brianfosterblack]

Thanks, i really appreciate this/

 

[DanteAmor]

This works perfectly.

Thank you very much

Without reaching the perfect, but in a functional way!

I'm glad to help you. Thanks for the feedback.