RND function same result after 5 runs !

[JohnSeito]

Hello people,

The RND function in VBA puzzles me.

The story goes like this, I have a large worksheet with a large set of
information in it. For example it has from line 2 to line 16k (I know this is not
a lot but just use this number as an example), with line one as the header.

What I am trying to do is put random number for each of these records from line 2
to link 16k. I used two methods to doing this,

ONE - the first is I used a loop that goes from
2 to the last row in the list, and store from 2 to the last number of the list in the collection.
I then randomly output from this collection and put them on to the list for each record, so a second
loop to randomly put the collected number for each record.

This is my code for the first method.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim C As New Collection, I As Integer

For I = 2 To lastRow
    C.Add CStr(I), CStr(I)
Next

For J = 2 To lastRow
    I = Int(Rnd * C.Count + 1)
    Cells(J, "H") = C.Item(I)
    C.Remove (I)
Next

SECOND method - I don't use collection at all, I just use the RND function to randomly choose a number from
1 to say 1 million and what ever random number that comes out from the RND function I put it next to the list.

For J = 2 To lastrow

     ranNUMBER = Int((1000000 * Rnd) + 1)
     Cells(J, "H") = ranNUMBER

Next

The problem I notice with both these two methods is that after I run it, and I run each of these method for like 5 times.
I noticed that for each record on the list, the random number is the same all the times I have run it. This is not what I had
in mind of what it was suppose to do, I thought if I run it the first time, each item on the list would have this number, and then
I run it again it would have a different number.

So row 225, column H. I run it 5 times, all 5 times it would give me the number for example 3456.

I am not sure why, could someone please help me understand what is going on. Thanks !!

 

[djreiswig]

Put "Randomize" at the top of your sub, then all of your random numbers will be random.

 

[bertie]

Use Randomize. This will generate a different, "seed", from which the random numbers are generated. See the sample code below.

Sub test()
   Dim str As String
   Dim i As Long
   
   For i = 1 To 9
      Randomize
      str = str & Int(9 * Rnd + 1)
   Next
   
   MsgBox str
End Sub

 

[djreiswig]

I don't think you need to have the Randomize in the loop. I just put it once at the top of the sub and it always seems to work fine.

 

[Cindy Ellis]

I used both of your code snippets above, and they each produced a different set of random numbers each time it was run, which looked reasonably random, with the main difference that the 1st method pulled values only from the set from 2 to the last data row #.
Your description implies that a "seed" is being provided, but if the excerpts you provided are the same as your code, that shouldn't be an issue. You might try using the statement "Randomize" on the line before you use Rnd, which randomizes the seed based on the system timer.
Hope that helps,

 

[AlphaFrog]

Rothstein suggests to Randomize only once before the loop and only once in a session.

http://www.mrexcel.com/forum/excel-questions/747715-rnd-not-truly-random.html#post3673953

 

[JohnSeito]

Thank you for your responses and answers to my question. I really appreciate it, is a big help.

So we have to randomize it everything time we use RND, so put it in the loop so it runs the randomize everytime I presumed.

 

[Rick Rothstein]

Thank you for your responses and answers to my question. I really appreciate it, is a big help.

So we have to randomize it everything time we use RND, so put it in the loop so it runs the randomize everytime I presumed.

I am guessing you did not follow the link that AlphaFrog included in Message #6.... no, you do not want to run it more than once per session. To find out why, and to find out how to write code to do that, follow the link AlphaFrog posted.

 

[JohnSeito]

I am looking into post number 6 from AlphaFrog and the
Link he provided now. AlphaFrog is an expert.
I'll see what comments I could give from that. Thanks !!

 

[JohnSeito]

Rothstein suggests to Randomize only once before the loop and only once in a session.

http://www.mrexcel.com/forum/excel-questions/747715-rnd-not-truly-random.html#post3673953

Hi AlphaFrog, Thanks for your message, I have been busy over the weekend. Hope you had a good weekend.

Was wondering who is Rothstein, and why only once before the loop, I put it in the loop and before the RND it looks like its working fine.

That link you provided has a lot of coding to it, I have to dig into it.

and the code provided by Reset, the first post from your link. It runs fine after a few click and then after that, the program
runs an infinite loop that is non-stop and I have to click on ctrl alt delete to get out of it.

Thanks !!!