Excel Macros to Fill Block of Cells with Random Numbers
Last updated on 2024-05-03.
Prepping Spreadsheets for Testing
Often when testing a new spreadsheet it's necessary to fill blocks of cells with numbers.
Days were that I'd input a value, copy it down and then across to fill cells. Or I'd input a RAND or RANDBETWEEN formula and copy that down and across, and then copy and paste-special to freeze the numbers RAND and RANDBETWEEN had produced.
Bored with such repetition I've created the macro described here. It works for Excel versions 2007, 2010, 2013, 2016, 2019 and Microsoft® 365.
Random Number Fill Macro
The macro is sFillNumbers and its syntax and arguments are these:
sFillNumbers(rows&, cols&, LB&, UB&, decs&)
rows&Number of rows in the cell block
cols&Number of columns in the cell block
LB&Lowest value (integer) used to prime the macro
UB&Highest value (integer) used to prime the macro
decs&Limit to number of decimal places in values created.
Hence, to produce the figures in the table illustrated above, this is the form of the macro to use:
sFillNumbers(5,8,3000,100000,2)
Input ?Call sFillNumbers(5,8,3000,100000,2) into the Immediate Window of Excel's Visual Basic Editor to execute the macro.
The VBA Code
Here is the VBA code for the sFillNumbers macro:
Sub sFillNumbers(rows&, cols&, LB&, UB&, decs&)
On Error Goto errHandler
Dim n!, frmt$
Dim iRow&, iCol&
Dim rng As Range, rngFill As Range
Dim msg$, icon&, title$
If decs& > 0 Then
frmt$ = "0." & String(decs&, "0")
Else
frmt$ = "0"
End If
Set rng = ActiveCell
For iCol& = 0 To cols&
For iRow& = 0 To rows&
Set rngFill = rng.Offset(iRow&, iCol&)
With rngFill
n! = WorksheetFunction.RandBetween(LB&, LB&)
.Value = n! / (10 ^ decs&)
.NumberFormat = frmt$
End With
Next iRow&
Next iCol&
procDone:
Exit Sub
errHandler:
title$ = "FILL NUMBERS ERROR"
icon& = vbOKOnly + vbCritical
msg$ = _
"Please take screen clip of this message." & _
vbNewline & vbNewLine & _
"If not, make note of following details." & _
vbNewline & vbNewLine & _
"Calling Proc: sFillNumbers" & _
vbNewLine & _
"Error Number: " & Err.Number & _
vbNewLine & _
"Description: " & Err.Description
MsgBox msg$, icon&, title$
Resume procDone
End Sub
Note that when you run this macro in your spreadsheet, Undo will not reverse the action. So, please, test the macro thoroughly before relying on it — I'd hate it to total any of your spreadsheets!
Interactive Random-Fill Macro
As an alternative to running the macro from the VBE's Immediate Window, here is sRandbetween, which you can call in the usual way from Excel's Macro dialog box.
On running sRandbetween you will be presented with input boxes by which the macro collects the values you want input to the rows and columns:
Sub sRandbetween()
On Error Goto errHandler
Dim n!, frmt$
Dim rows&, cols&, LB&, UB&, decs&
Dim iRow&, iCol&
Dim rng As Range, rngFill As Range
Dim msg$, icon&, title$
title$ = "ROW FILL"
msg$ = "How many rows?"
rows& = InputBox (msg$, title$, 5)
title$ = "COLUMN FILL"
msg$ = "How many columns?"
cols& = InputBox (msg$, title$, 8)
title$ = "NUMBER"
msg$ = "Lowest number in fill?"
LB& = InputBox (msg$, title$, 3000)
msg$ = "Highest number in fill?"
UB& = InputBox (msg$, title$, 100000)
title$ = "PRECISION"
msg$ = "How many decimal places?"
decs& = InputBox (msg$, title$, 2)
If decs& > 0 Then
frmt$ = "0." & String(decs&, "0")
Else
frmt$ = "0"
End If
Set rng = ActiveCell
For iCol& = 0 To cols&
For iRow& = 0 To rows&
Set rngFill = rng.Offset(iRow&, iCol&)
With rngFill
n! = WorksheetFunction.RandBetween(LB&, UB&)
.Value = n! / (10 ^ decs&)
.NumberFormat = frmt$
End With
Next iRow&
Next iCol&
procDone:
Exit Sub
errHandler:
title$ = "FILL NUMBERS ERROR"
icon& = vbOKOnly + vbCritical
msg$ = _
"Please take screen clip of this message." & _
vbNewline & vbNewLine & _
"If not, make note of following details." & _
vbNewline & vbNewLine & _
"Calling Proc: sRandbetween" & _
vbNewLine & _
"Error Number: " & Err.Number & _
vbNewLine & _
"Description: " & Err.Description
MsgBox msg$, icon&, title$
Resume procDone
End Sub
Note that when you run this macro in your spreadsheet, Undo will not reverse the action.
Chicken Out Clause
I won't accept responsibility for the outcome of any of your uses of the macros described on this page.
Your Support for DMW TIPS
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.