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
ranNum = Application.RoundUp(Rnd() * 6000, 0)
If Application.CountIf(Range(“B:B”), ranNum) > 0 Then
GoTo back
Range(“B” & Rows.Count).End(xlUp).Offset(1, 0).Value = ranNum
End If
End Sub

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

3 thoughts on “Generating permanent random numbers in Excel for Statistical Analysis

  1. Hello Sir
    thanks a lot for your amazing solutions for all related excel problems
    asking your help and regular support for my case
    i have userform and save date on sheet using this for ; what i need is how to generate serial number including text but this number while be generated based on task name ; for example : “Production1001” ; “Production1002” ; “Production1003” OR “Service2001” ; “Service2002” ; “Service2003”
    on other words i need to create unique number for each item including this item name
    thanks again sir ; and wish your kindly feedback

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.