RND function same result after 5 runs !

JohnSeito

Active Member
Joined
Nov 19, 2007
Messages
390
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.

Code:
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.

Code:
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 !!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

djreiswig

Well-known Member
Joined
Mar 13, 2010
Messages
523
Put "Randomize" at the top of your sub, then all of your random numbers will be random.
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Use Randomize. This will generate a different, "seed", from which the random numbers are generated. See the sample code below.

Rich (BB code):
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

Well-known Member
Joined
Mar 13, 2010
Messages
523
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802

ADVERTISEMENT

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,
 

JohnSeito

Active Member
Joined
Nov 19, 2007
Messages
390

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Nov 19, 2007
Messages
390
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 !! :)
 
Last edited:

JohnSeito

Active Member
Joined
Nov 19, 2007
Messages
390
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 !!! :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,715
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top