Generate Random Number Table

  • Thread starter Thread starter Tim Bieri
  • Start date Start date
T

Tim Bieri

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
 
Hi Tim,

try:

For Each cell In Range("name")

cell.Formula = "=RND()"

Next cell


Thats even shorter ;o)

Best

Markus
 
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.
 
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
 
Back
Top