Generate Random Number Table

Howdy,

I would like to generate a random number in a named range. The named range
would have a variable size (up to 25000 row and 50 column). I know that I
can copy/paste RND() in all the cells, but I am interested in generating the
number. Using RND(), I would have to copy paste values so they would not
change. I am also concerned about looping that many times and how long that
would take.

Is there an easy way to essentially populate a 2-D array from a random
function?

Regards,
TB

This will be fairly quick:

Public Sub FillNamedRangeWithRandoms()
Dim vArr As Variant
Dim i As Long
Dim j As Long
With Range("MyRange")
ReDim vArr(1 To .Rows.Count, 1 To .Columns.Count)
For i = 1 To UBound(vArr, 1)
For j = 1 To UBound(vArr, 2)
vArr(i, j) = Rnd
Next j
Next i
.Value = vArr
End With
End Sub


In article <BE302ADF.10ED%tbieri@gci.net>, Tim Bieri <tbieri@gci.net>
wrote:

> Howdy,
>
> I would like to generate a random number in a named range. The named range
> would have a variable size (up to 25000 row and 50 column). I know that I
> can copy/paste RND() in all the cells, but I am interested in generating the
> number. Using RND(), I would have to copy paste values so they would not
> change. I am also concerned about looping that many times and how long that
> would take.
>
> Is there an easy way to essentially populate a 2-D array from a random
> function?
>
> Regards,
> TB

Hi Tim,

try:

For Each cell In Range("name")

cell.Formula = "=RND()"

Next cell


Thats even shorter ;o)

Best

Markus


>-----Original Message-----
>Howdy,
>
>I would like to generate a random number in a named
range. The named range
>would have a variable size (up to 25000 row and 50
column). I know that I
>can copy/paste RND() in all the cells, but I am
interested in generating the
>number. Using RND(), I would have to copy paste values
so they would not
>change. I am also concerned about looping that many
times and how long that
>would take.
>
>Is there an easy way to essentially populate a 2-D array
from a random
>function?
>
>Regards,
>TB
>
>.
>

and one more

Sub GenRandom()
With Range("MyRange")
.Formula = "=rand()"
.Formula = .Value
End with
End Sub
Doesn't require any looping and the numbers produced won't change.
Nonetheless, it may be slower than JE's solution depending on how
calculation gets involved. I don't know since I haven't tested it.

--
Regards,
Tom Ogilvy

"Tim Bieri" <tbieri@gci.net> wrote in message
news:BE302ADF.10ED%tbieri@gci.net...
> Howdy,
>
> I would like to generate a random number in a named range. The named
range
> would have a variable size (up to 25000 row and 50 column). I know that I
> can copy/paste RND() in all the cells, but I am interested in generating
the
> number. Using RND(), I would have to copy paste values so they would not
> change. I am also concerned about looping that many times and how long
that
> would take.
>
> Is there an easy way to essentially populate a 2-D array from a random
> function?
>
> Regards,
> TB
>

Howdy,

Thanks to all for the great responses.

In an unscientific test, both (Tom and JE) methods appear to be about equal,
if you turn off the screen update. They are pretty close to equal with
screens update on, with Tom's updating twice. Either way, they are less than
5 seconds for 25k x 50.

Regards
TB

"Tim Bieri" wrote:

> Howdy,
>
> I would like to generate a random number in a named range. The named range
> would have a variable size (up to 25000 row and 50 column). I know that I
> can copy/paste RND() in all the cells, but I am interested in generating the
> number. Using RND(), I would have to copy paste values so they would not
> change. I am also concerned about looping that many times and how long that
> would take.
>
> Is there an easy way to essentially populate a 2-D array from a random
> function?
>
> Regards,
> TB
>
>