Random is not really random

[Susan]

i have a sub that chooses a random book of the bible (list of books is
on Sheet1) & inserts the name of the book in a textbox. after that
book has been chosen, the row gets hidden so it doesn't get chosen
again.

i open the spreadsheet & press the "select" button which runs the
sub. i get (after pressing the button 3x):
1. Acts
2. Philippians
3. Jonah

ok, fine. close the workbook without saving (so those rows don't get
hidden). open the workbook, and press the button 3x again. i get:
1. Acts
2. Philippians
3. Jonah.

so the response is technically random, but not really random. any
ideas? i can live with it the way it is, but i'm just curious as to
why i don't get different books the 2nd time i press the button 3x.
(at this point most of the books are visible.) the pertinent code
follows:
=========================
Private Sub cmdSelect_Click()
Dim ws1 As Worksheet
Dim rVis As Range
Dim iRnd As Long
Dim iRow As Long
Dim cell As Range
Dim RandomBook As String
Dim myBook As Range

Set ws1 = ActiveWorkbook.Worksheets("Sheet1")

'find a random bible book from column A
'from the visible cells only (ones not chosen
'previously)
Set rVis = ws1.Range("A1", ws1.Range("A1").End(xlDown)) _
.SpecialCells(xlCellTypeVisible)
iRnd = Int(rVis.Count * Rnd) + 1

For Each cell In rVis
iRow = iRow + 1
If iRow = iRnd Then Exit For
Next cell

iRow = cell.Row
Set myBook = ws1.Range("a" & iRow)

RandomBook = myBook.Value

'enter the bible book in the textbox
Me.txtBook.Value = RandomBook

myBook.EntireRow.Hidden = True

End Sub
==============================
thanks in advance for any insight.

susan

 

[Bernie Deitrick]

Try

Randomize
iRnd = Int(rVis.Count * Rnd) + 1


HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

All random number generators work on what is called a seed.
If the seed is the same from Run1 to Run2 then the same numbers are
generated. They are random in the sense that there is no (almost "no")
relationship between one number and the next.

The initialize the seed to another value each time use the Randomize command

Set rVis = ws1.Range("A1", ws1.Range("A1").End(xlDown)) _
.SpecialCells(xlCellTypeVisible)

Randomize ' Initialize
random-number generator

iRnd = Int(rVis.Count * Rnd) + 1

From VBA Help (in XL 2003)
Randomize uses number to initialize the Rnd function's random-number
generator, giving it a new seed value. If you omit number, the value
returned by the system timer is used as the new seed value.

If Randomize is not used, the Rnd function (with no arguments) uses the same
number as a seed the first time it is called, and thereafter uses the last
generated number as a seed value.

Note To repeat sequences of random numbers, call Rnd with a negative
argument immediately before using Randomize with a numeric argument. Using
Randomize with the same value for number does not repeat the previous
sequence.


best wishes

 

[Susan]

hey!!!!! wonderful! thanks a lot bernie!!!!

susan

 

[Susan]

thanks bernard for explaining it! someone else had given me the
portion of code to make it random & i guess they didn't know about
"Randomize"........ & i didn't understand that part of the code.
thanks again

susan