When answering newsgroup Excel questions, or doing testing for client projects, I often create a quick table, with a few rows and columns. The headings, such as a list of months or weekdays can be generated by using AutoFill. That just leaves the body of the table, where I want some numbers for the test.

I used to calculate numbers with a RAND or RANDBETWEEN formula, then copy and paste the results as values. But that’s a lot of steps when you’re in a hurry, so I built my own number generator, that runs with a click or two. The values aren’t important, I just want something to populate a table, pivot table or quick chart.

I wrote macros to generate numbers in different ranges, and added these to a toolbar menu. You could add toolbar buttons, or assign shortcut keys to run them. In Excel 2007, add them to the Quick Access Toolbar.

My macro code is shown below. Do you have a different way to create dummy numbers for testing?

‘====================================
Sub SRandRange()
Dim c As Range
Dim x As Long, y As Long
Application.EnableEvents = False
Application.ScreenUpdating = False

x = Application.InputBox(”Start Number”)
y = Application.InputBox(”End Number”)

For Each c In Selection
c.Value = randbetween(x, y)
Next

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
‘====================================
Sub SRand10()
Dim c As Range
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each c In Selection
c.Value = Evaluate(”=ROUND(RAND()*10,0)”)
Next c

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
‘====================================

Filed under: Excel