Generate list of random numbers, using same starting seed

[axf80]

I have inherited some VBA code in Excel 2016 - when the code is run, 500 users are input one by one, and a random number assigned to each one.

As far as I understand, the code uses Randomize and Rnd (-1) to set the same starting seed each time, so that every time 500 users are input the same 500 random numbers are used.

I need to output a list of these 500 random numbers.

So far I have used this code:

Private Sub CommandButton1_Click()




Randomise_Range (Sheets("Sheet1").Range("A1:A500"))


End Sub


Sub Randomise_Range(Cell_Range As Range)
Dim Cell


Application.ScreenUpdating = False


For Each Cell In Cell_Range
Cell.Value = Rnd(-1)
Randomize (1)
Next Cell


Application.ScreenUpdating = True




End Sub

However, this outputs 500 random copies of the SAME random number! How do I get the 500 different random numbers?

Many thanks

 

[MickG]

Try:-

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
Randomise_Range (Sheets("Sheet1").Range("A1:A500"))
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

[COLOR=navy]Sub[/COLOR] Randomise_Range(Cell_Range [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Cell
Randomize
Application.ScreenUpdating = False
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Cell [COLOR=navy]In[/COLOR] Cell_Range
        Cell.Value = Int(Rnd * 500) + 1 
    [COLOR=navy]Next[/COLOR] Cell
Application.ScreenUpdating = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[axf80]

Hi Mick,

Thanks for your reply. I want the random numbers to be between zero and one, so I changed "Int(Rnd * 500) + 1" to just "Rnd".

However, this code seems to produce a different set of 500 random numbers each time I run it. I need it to produce the same 500 random numbers. I thought including "Randomize" in the code should achieve this by setting the first starting seed?

If I change it to Rnd (-1) I again get the SAME random number, 500 times.

Thanks

 

[MickG]

This will produce 500 Unique numbers between Zero & One, but you will need to store them somewhere for future use, in order to use the same numbers again, perhaps in another sheet or Hard code them in a Macro. !!

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
Randomise_Range (Sheets("Sheet1").Range("A1:A500"))
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]Sub[/COLOR] Randomise_Range(Cell_Range [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Cell [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] rNum [COLOR=navy]As[/COLOR] Double
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Application.ScreenUpdating = False
    [COLOR=navy]Do[/COLOR] Until c = 500
       rNum = Rnd * 1
        [COLOR=navy]If[/COLOR] Not .Exists(rNum) [COLOR=navy]Then[/COLOR]
           c = c + 1
            .Add rNum, Nothing
       [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Loop[/COLOR]
Range("A1:A500") = Application.Transpose(.keys)
[COLOR=navy]End[/COLOR] With
Application.ScreenUpdating = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[axf80]

Thanks Mike,

Unfortunately my issue is that I need the 500 random numbers to be the same each time, and this code doesn't achieve that. Otherwise I would just use the formula "=rand()" in 500 cells.

Perhaps there is a way to set the initial seed, and then use the subsequent random number as a seed for the next random number, and so on. However, I can't figure out how to implement this.

 

[MickG]

Recalling:-
You could Store them in a spare sheet of your workbook and recall as required !!:-

 

[axf80]

But that isn't what I want to do. I need to see the random numbers that Excel will generate starting from the given initial seed used when Rnd (-1) is called. I want each successive call to the Rnd function to use the previous number as a seed for the next number in the sequence.

 

[shg]

Sub axf()
  Dim i As Long
  
  Rnd -1 ' pick a negative number you like
  
  For i = 1 To 5
    Debug.Print Rnd
  Next i
End Sub

Look in the Immediate window and run that twice.

 

[MickG]

Very neat, it's even cause me to read the "Rnd" Help.!!

 

[axf80]

Thank you very much!