Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().
However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.
This is OK if you want a small list, but what if you have to generate say a list of 1000 random numbers?
The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.
Sub GenNumbers()
Dim i As Integer, Row As Integer, Col As Integer
Dim NoRows As Integer, NoDigits As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 ‘ Write random numbers on column A
‘Get the number of rows and number of digits entered by the user
NoRows = ActiveSheet.Range(“B1″).Value
NoDigits = ActiveSheet.Range(“B2″).Value
‘Calculate minimum and maximum numbers to be generated
MinNum = WorksheetFunction.Power(10, (NoDigits – 1))
MaxNum = WorksheetFunction.Power(10, NoDigits) – 1
‘Construct the formula to be entered in the cells.
NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)”
‘ The loop to generate the numbers
i = 0
For Row = 3 To NoRows + 2
i = i + 1
ActiveSheet.Cells(Row, Col).Value = i
ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula
Next Row
End Sub
How to use the macro
1. Switch to Visual Basic Editor and Insert a new module.
2. Copy the code into the new module.
3. Close Visual Basic Editor and return to your worksheet.
4. In cell B1 of your worksheet enter the number entries you want.
5. In cell B2 of your worksheet enter the number digits you want.
6. Go to Tools -> Macro -> Macros, you will see the list of available macros.
7. Select GenRandomNum and run it.
8. You will get an output like this:
Applies to: Excel 2003