DMW logo
contact@consultdmw.com

Excel Macros to Fill Block of Cells with Random Numbers

How to code Excel VBA macros for filling blocks of cells with random numbers when numbers are needed to check the workings of a spreadsheet.

Last updated on 2022-10-26 by David Wallis.


Preface

Often when testing a new spreadsheet it’s necessary to fill blocks of cells with numbers. There are several techniques you can use to achieve this. And Excel can assist you: the RAND and RANDBETWEEN functions are useful in this respect.

Days were that I’d input a value, copy it down and then across to fill cells. Or would input a RAND formula and copy that down and across, and then copy and paste-special to freeze the numbers RAND 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

Block of random numbers

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. DMW Consultancy Ltd will not 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.

“A good choice of number representation is essential for efficient calculation. Try, for example, to multiply XXVII by XXXIX!”

SH Hoolingdale and GC Tootill, Electronic Computers (1965)