Array of Random Numbers (XP)

Topic

New Reply

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?

Reply | Quote

Replies

See if the replies to post 350349 and post 162299 (plus links provided in the replies) help.

Reply | Quote

Thanks for the quick answers.
Problem is solved.

Horst

Reply | Quote

Thanks for the quick answers.
Problem is solved.

Horst

Reply | Quote

See if the replies to post 350349 and post 162299 (plus links provided in the replies) help.

Reply | Quote

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.

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

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote