Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array of Random Numbers (XP)

    Thanks for the quick answers.
    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
  •