Its time for some more fun with another Excel #MacroMonday blog post in my 20201 series of #Excel and VBA Tips. Today let’s look at a useful tool to generate random numbers in a selected range of cells. I use this regularly to populate a range of cells in an Excel workbook with random values to test my spreadsheet solutions.

To have this in your VBA Macro toolbox sure is a handy one!.

Storing The Excel Macro.

Before we begin to write any code you need to decide where to store the code. You have a choice.

To store your code either in your Personal Macro Workbook or

Store it in your current workbook.

What’s the difference?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Preparing To Write The Macro.

We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro.

In this example, I have called the macro RandomNumbers. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’] Sub RandomNumbers End Sub [/stextbox]

Declaring Variables.

We need to declare a variable.  This ensures that Excel creates a memory container for this value.  In this example, we need to declare the following. This ensures the user selected range is stored in the Excel memory.

[stextbox id=’info’] Dim rng as Range [/stextbox]

Setting The Range.

Next, we set the range to be the user selection on the Excel worksheet. This is a quick way to set a variable selection of cells.

[stextbox id=’info’] Set rng = Selection [/stextbox]

Using The For Each Loop.

This is where Excel steps through each of the cells in the user-selected range and enters a random value into each of the cells. As each of the cells is looped, a random number from the specified range of values is entered into it. I have chosen to use random numbers from the range of 1 to 5000. Excel continues to loop through all of the cells in the selected range until all the all contain a random number from the range.

[stextbox id=’info’] For Each cell In rng cell.Value = WorksheetFunction.RandBetween(1, 5000) Next [/stextbox]

Ending The Excel Macro.

The code ends once all looping of cells has been completed with the “End Sub" piece of code.  This was already entered into the module for us when started the type the name of the macro.

[stextbox id=’info’] End Sub [/stextbox]

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

 

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

Clear All Cells With Values Greater Than Zero