# RND function same result after 5 runs !

#### JohnSeito

##### Active Member
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
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 !!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### djreiswig

##### Well-known Member
Put "Randomize" at the top of your sub, then all of your random numbers will be random.

#### bertie

##### Well-known Member
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
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

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

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

Replies
13
Views
1K
Replies
3
Views
435
Replies
3
Views
383
Replies
4
Views
359
Replies
1
Views
136

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,987
Messages
5,834,723
Members
430,314
Latest member
Kym_beginner_in_VBA

### 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.

### Which adblocker are you using?

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

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