There are many ways to do this. This macro is based on a recorded macro that I edited.
I need to generate random numbers for my example data for blog posts and training manuals. This macro makes it quicker.
Select a range and run the macro. It will populate the range with random numbers from 1,000 to 10,000 formatted with the comma separator – see image below.
The macro that does the work is.
Sub RandomNumbers()
' generate random numbers in selected cells
With Selection
.Formula = "=RANDBETWEEN(1000,10000)"
.Value = .Value
.NumberFormat = "#,##0"
End With
End SubYou can edit the numbers in the RANDBETWEEN function in the code to change the range of random numbers.
This macro is useful if you are need values to test a formula or create examples.
Here is another macro that will also do this…
Sub RandomNumbers()
Dim Cell As Range
For Each Cell In Selection
Cell = Evaluate(“RANDBETWEEN(1000,10000)”)
Next
Selection.NumberFormat = “#,##0”
End Sub
I recognize that assigning values to individual cells can be slow, but I am not sure if it would necessarily be slower than assigning formulas to all the cells in the selection (even all at once) and then (in essence) pasting the calculated values over top of the formulas. For small to medium selections, it probably doesn’t matter, but if a huge number of cells are involved, then I am not sure which method would be more efficient. But, if a huge number of cells are involved, then this version of my macro would be much faster…
Sub RandomNumbers()
Dim Ary As Variant
Dim X As Variant
Dim Y As Variant
With Selection
Ary = .Cells
For X = LBound(Ary) To UBound(Ary, 1)
For Y = LBound(Ary) To UBound(Ary, 2)
Ary(X, Y) = Evaluate(“RANDBETWEEN(1000,10000)”)
Next
Next
.Value = Ary
.NumberFormat = “#,##0”
End With
End Sub
As always thanks for sharing Rick
A further tweak to your first code – remove EVALUATE and use the square brackets [ ] which is shorter and means you don’t need quotation marks.
Sub RandomNumbers()
Dim Cell As Range
For Each Cell In Selection
Cell = [=RANDBETWEEN(1000,10000)]
Next
Selection.NumberFormat = “#,##0”
End Sub
Thanks again.
This was the first VBA code I have written in over a year… I forgot about all about the square bracket shortcut for Evaluate.
Wow I thought you still coded a lot – but that maybe was a while ago now.
I use the square brackets all the time to easily refer to range names in VBA code.
All the best.
Before dynamic arrays, yes, 80% or more of my time was spent writing VBA code, but once I got XL365 and all the “good” functions were released to the entire Excel community, I’d say 99% of my time is writing formulas now.