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

Try

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


HTH,
Bernie
MS Excel MVP


"Susan" wrote in message
...
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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message
...
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

hey!!!!! wonderful! thanks a lot bernie!!!!
:)
susan


On Feb 20, 2:00*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Try

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

HTH,
Bernie
MS Excel MVP

"Susan" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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


On Feb 20, 2:08*pm, "Bernard Liengme"
wrote:
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
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message

...



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- Hide quoted text -

- Show quoted text -