# Thread: Array of Random Numbers (XP)

1. ## Array of Random Numbers (XP)

Hi Folks,
I need a routine for creating an array of random numbers as XLs RND functions delivers 5-15 % duplicates (for my range from 1-321).
Can anyone help me?

2. ## Re: Array of Random Numbers (XP)

See if the replies to <post#=350349>post 350349</post#> and <post#=162299>post 162299</post#> (plus links provided in the replies) help.

3. ## Re: Array of Random Numbers (XP)

Your question is not completely clear. It sounds like you are trying to create an array with the integers 1 to 321 arranged in random order. If that is what you want, the code below will put the list in A1:A321 of sheet Sheet1. It can easily be modifed to put the list into an array.

<pre>Public Sub Rand321()
Dim iNum(1 To 321) As Integer, iMax As Integer
Dim I As Integer, J As Integer, iRand As Integer
For I = 1 To 321
iNum(I) = I
Next I
Randomize
iMax = 321
For I = 1 To 321
iRand = Int(iMax * Rnd + 1)
Worksheets("Sheet1").Range("A1").Offset(I - 1, 0).Value = iNum(iRand)
For J = iRand To iMax - 1
iNum(J) = iNum(J + 1)
Next J
iMax = iMax - 1
Next I
End Sub
</pre>

4. ## Re: Array of Random Numbers (XP)

I'm not sure you really need a routine. You can put the =rand() function into cells A1:A321. Then in cells B1, put in =RANK(A1,\$A\$1:\$A:\$321). Then copy B1 down to B321. Column B will then have the integers 1-321 in random order. Just hit F9 to generate a new random list.

5. ## Re: Array of Random Numbers (XP)

Your method does have a small possibility of duplicating numbers. If RAND happens to generate two identical random numbers, the ranks for those two numbers will be the same.

6. ## Re: Array of Random Numbers (XP)

Problem is solved.

Horst

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•