I need a code to fill up an array with UNIQUE random numbers, say 50
random numbers from 100-250 range.

Ideally, I need this to be accomplished *without a Worksheet
interface*. I have a long-winded-and-dirty approach that generates the
random numbers on a worksheet and then have these copied into an array
but this method cramps the stlye of my project. Any help will be
appreciated.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=519080

Myles

Here's one way:

Sub test()

Dim i As Long
Dim vaUniques As Variant

vaUniques = UniRandArr(100, 250, 50)

For i = 1 To 50
Debug.Print vaUniques(i)
Next i

End Sub
Function UniRandArr(ByVal lLower As Long, _
ByVal lUpper As Long, _
ByVal lTotal As Long) As Variant

Dim i As Long
Dim aTemp() As Double
Dim aFinal() As Long

ReDim aTemp(1 To 2, lLower To lUpper)
Randomize

For i = lLower To lUpper
aTemp(1, i) = i
aTemp(2, i) = Rnd
Next i

SortArray aTemp
ReDim aFinal(1 To lTotal)

For i = 1 To lTotal
aFinal(i) = aTemp(1, i - 1 + lLower)
Next i

UniRandArr = aFinal

End Function

Sub SortArray(ByRef aTemp As Variant)

Dim i As Long, j As Long
Dim lTemp1 As Double, lTemp2 As Double

For i = LBound(aTemp, 2) To UBound(aTemp, 2) - 1
For j = i To UBound(aTemp, 2)
If aTemp(2, i) aTemp(2, j) Then
lTemp1 = aTemp(1, i)
lTemp2 = aTemp(2, i)
aTemp(1, i) = aTemp(1, j)
aTemp(2, i) = aTemp(2, j)
aTemp(1, j) = lTemp1
aTemp(2, j) = lTemp2
End If
Next j
Next i

End Sub


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

Myles wrote:
I need a code to fill up an array with UNIQUE random numbers, say 50
random numbers from 100-250 range.

Ideally, I need this to be accomplished *without a Worksheet
interface*. I have a long-winded-and-dirty approach that generates
the random numbers on a worksheet and then have these copied into an
array but this method cramps the stlye of my project. Any help will be
appreciated.

Myles.

Hello,

I suggest to take my UDF UniqRandInt() from www.sulprobil.com.

HTH,
Bernd

How about this one:

Sub test009890()
Dim x As Long ' just a counter
Dim y As Long ' just a counter
Dim u As Long ' upperbound
Dim l As Long ' lowerbound
Dim a(1 To 50) As Long ' the array
l = 100
u = 250
Randomize
For x = 1 To 50
a(x) = ((u - l + 1) * Rnd + l)
Next
For x = 1 To 50
For y = 1 To 50
While a(x) = a(y) And x < y
a(y) = ((u - l + 1) * Rnd + l)
Wend
Next
Debug.Print Format(x, "00") & ": " & a(x)
Next

End Sub

--

Helmut Weber

Dick:

Your solution is spot on! Just the one the Doctor ordered! Many
thanks.

Helmut:

Your code somehow deliver repeats. This is easily assayed when the
range from which the random numbers are to be drawn is narrowed - eg
drawing 10random numbers from 1-15.


Bernd:

The critical part of my request is to generate the random numbers
without having recourse to the worksheet.

To all, I appreciate the effort to help.


Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=519080

Hello Myles,

Aha. Then I should have suggested my UDF VBUniqRandInt() :-)

But you already have a solution.

Regards,
Bernd