One way:

Range("A1:J1000").Formula = "=RAND()"

This will insert the formula, which will recalculate unless you turn
Calculation to Manual.

You could convert the formulae to values so they won't recalculate:

With Range("A1:J1000")
.Formula = "=RAND()"
.Value = .Value
End With

Another way, that inserts the value of VBA's Rnd method, not a formula:

Dim rng As Range
Dim vArr As Variant
Dim i As Long
Dim j As Long
Set rng = Range("A1:J1000")
ReDim vArr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For i = 1 To UBound(vArr, 1)
For j = 1 To UBound(vArr, 2)
vArr(i, j) = Rnd
Next j
Next i
rng.Value = vArr

In article . com,
wrote:

Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

That is, I want to have a program/macro to populate the cells
instead of having to replicate them manually (e.g, by dragging),
which is painfully slow for such large numbers of cells. If
there is an alternative that is equally fast and easy to use,
I would appreciate hearing about it.

I would also appreciate step-by-step instructions for how to
enter the macro and how to execute it.

And since I am interested in an expression involving RAND(),
I would appreciate advice on how to prevent re-execution of
the macro each time the spreadsheet is recalculated -- other
than turning off automatic recalculation. (But if that is the
only way, I would appreciate confirmation.) Or is that the
norm; i.e, are macros executed only when you invoke them
explicitly from the keyboard?

I know I should read a book, and I will in time. But I am
hoping this is a simple enough request that someone will
not mind filling in the blanks sooner than I digest a book.

Thanks.