Hello Macro Monday time again. Today let’s look at writing a simple Excel Macro to generate some random numbers in Excel. I use random numbers and data a lot in my work building spreadsheets solutions, mainly for testing purposes of dummy data. This powerful little Macro will generate random number in a region of cells that we can specify and populate them with one click of a mouse. Sound good right?. So let’s get going.
Download Macro Mondays Example Files.
What Does The Macro Do?
The Macro will use the RND function to generate a list of random numbers in a range of cells that we select on the worksheet.
How Does It Work?
A Nested For Next Loop is used, where the For Loop is nested within a For Loop. Depending on the selection of the range of rows and columns. Suppose you make a selection of 10 rows and 3 columns, the value of the column varies from 1 to 3 and value of the rows varies from 1 to 10.
When the first For loop is run, the value of the columns is 1. It then moves to the second For loop which runs 10 times (for each row).
Once the second For loop has been executed 10 times, it goes back to the first For loop where now the value of the columns becomes 2., and again the next For loop runs for 10 times until all cells have been looped through within the macro routine. Let’s take a close look at at our specific example and step through each part of the code.