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:
For i = 1 To 1000
ranNum = Application.RoundUp(Rnd() * 6000, 0)
If Application.CountIf(Range(“B:B”), ranNum) > 0 Then
Range(“B” & Rows.Count).End(xlUp).Offset(1, 0).Value = ranNum
Watch the training video below to learn how to generate unique and permanent numbers for simulations and statistical analysis: