Filling Array with uniqur random numbers
 

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

Filling Array with uniqur random numbers
 

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.

Filling Array with uniqur random numbers
 

Hello,

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

HTH,
Bernd

Filling Array with uniqur random numbers
 

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

Filling Array with uniqur random numbers
 

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

Filling Array with uniqur random numbers
 

Hello Myles,

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

But you already have a solution.

Regards,
Bernd