Generating permanent random numbers in Excel for Statistical Analysis

How to generate permanent random numbers in Excel for statistical analysis using VBA
A question from a website visitor:
I’m using the Random Function in MS Excel to generate a thousand numbers but each time I press F9 all the random numbers change and my results based on functions change. Is it possible to generate, let’s say, a thousand unique whole numbers between 1 and 6000 in one single column which don’t change so that I can perform statistical analysis on them.
Typing large amount of numbers into an Excel worksheet is a boring and unproductive method for doing simulation studies. You need an automated method. As we showed earlier you can generate random numbers by using the Rand() or RANDBETWEEN() excel functions to automatically generate random numbers. One of the main disadvantage of generating numbers in this fashion is the fact that they keep on changing when you press F9 from the keyboard. Now when you have associated formulas with these numbers the results also change and that may not be exactly what you wanted to do!

The Excel VBA code or macro for the problem is given below:

Sub RandomNumbers()
For i = 1 To 1000
back:
ranNum = Application.RoundUp(Rnd() * 6000, 0)
If Application.CountIf(Range(“B:B”), ranNum) > 0 Then
GoTo back
Else
Range(“B” & Rows.Count).End(xlUp).Offset(1, 0).Value = ranNum
End If
Next
End Sub

Watch the training video below to learn how to generate unique and permanent numbers for simulations and statistical analysis:


One thought on “Generating permanent random numbers in Excel for Statistical Analysis

  1. Pingback: Automating dash boards using list box form control with choose function | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *