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