I want to create two excel sheets. One to print OT the other to print NT. How
do I program a variable to give me a 5 by 5 print out of 27 or 39 names
(books of the bible) leaving the center blank. So I can print out BINGO
sheets. Having everything centered. And the size of the boxes equal.

See if this will get you started, download bingo cards, almost at the bottom
of the page

http://www.contextures.com/excelfiles.html
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"BArtneedsHELP" wrote in message
...
I want to create two excel sheets. One to print OT the other to print NT.
How
do I program a variable to give me a 5 by 5 print out of 27 or 39 names
(books of the bible) leaving the center blank. So I can print out BINGO
sheets. Having everything centered. And the size of the boxes equal.

Paul, Thank you

I am not permitted to download Word or Excel files to my computer.
So this option isn't going to work for me

I'm trying to learn to write the functions myself, I'm not doing so good.
practice practice practice
I'd prefer to cut and paste like how I learned HTML

To person with two names
Give the following code a try...
'----------------------------------------------------------------
Option Explicit

Sub BibleBingo()
'Jim Cone - December 17, 2004
'Creates two bingo type cards using book names from the Bible.
'Assigns names of books from the Bible, at random, to the
'first two worksheets in the active workbook.
'The first sheet will have old testament book names and
'the second sheet will have new testament names.
'The worksheets must be manually formatted in
'cells "C12:G16" to look like bingo cards.

Dim varOldTest As Variant
Dim varNewTest As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngNum As Long
Dim lngIndex As Long
Dim arrNums() As Long
Dim arrBooks() As String

varOldTest = Array("Genesis", "Exodus", "Leviticus", "Numbers", _
"Deuteronomy", "Joshua", "Judges", "Ruth1", "Samuel2", _
"Samuel1", "Kings2", "Kings1", "Chronicles2", "Chronicles", _
"Ezra", "Nehemiah", "Esther", "Job", "Psalms", "Proverbs", _
"Ecclesiastes", "Song of Solomon", "Isaiah", "Jeremiah", _
"Lamentations", "Ezekiel", "Daniel", "Hosea", "Joel", _
"Amos", "Obadiah", "Jonah", "Micah", "Nahum", "Habakkuk", _
"Zephaniah", "Haggai", "Zechariah", "Malachi") '39

varNewTest = Array("Matthew", "Mark", "Luke", "John", "Acts", _
"Romans1", "Corinthians2", "Corinthians", "Galatians", _
"Ephesians", "Philippians", "Colossians1", _
"Thessalonians2", "Thessalonians1", "Timothy2", _
"Timothy", "Titus", "Philemon", "Hebrews", "James1", _
"Peter2", "Peter1", "John2", "John3", "John", "Jude", _
"Revelation") '27

For lngIndex = 1 To 2
If lngIndex = 1 Then lngNum = 38 Else lngNum = 26
ReDim arrNums(0 To lngNum)
ReDim arrBooks(1 To 5, 1 To 5)
For i = 1 To 5
For j = 1 To 5
Do
Randomize (Right(Timer, 2) * i)
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
k = Int((lngNum + 1) * Rnd)
'prevents duplicates
If arrNums(k) < 999 Then
If lngIndex = 1 Then
arrBooks(i, j) = varOldTest(k)
arrNums(k) = 999
Else
arrBooks(i, j) = varNewTest(k)
arrNums(k) = 999
End If
End If
Loop Until arrBooks(i, j) < vbNullString
Next 'j
Next 'i
arrBooks(3, 3) = "FREE"
'put names on worksheets
Worksheets(lngIndex).Range("C12:G16").Value = arrBooks()
Next 'lngIndex
End Sub
'------------------------------------------------------------------------

Jim Cone
San Francisco, CA

"BArtneedsHELP" wrote in message
...
I want to create two excel sheets. One to print OT the other to print NT. How
do I program a variable to give me a 5 by 5 print out of 27 or 39 names
(books of the bible) leaving the center blank. So I can print out BINGO
sheets. Having everything centered. And the size of the boxes equal.

Paul B Wrote:
See if this will get you started, download bingo cards, almost at the
bottom
of the page

http://www.contextures.com/excelfiles.html
--
Paul B


I'm also looking to create some Bingo crads, and this file has been a
huge help! I've entered all of my words into the second sheet, and
it's creating one perfectly randomized Bingo card! Unfortunately, I
can't get it to produce _more_ than one. Even though the formulas are
the same as the ones in the sample file, all I get on the additional
cards is #NUM! for the results. As an example:

Here's the code for the first column on the original card -

=INDEX(Sheet2!$A$1:$A$9,MATCH(LARGE(Sheet2!$B$1:$B $9,ROW()-1),Sheet2!$B$1:$B$9,0))

Here's the code for the same column on the second card -

=INDEX(Sheet2!$A$1:$A$9,MATCH(LARGE(Sheet2!$B$1:$B $9,ROW()-3),Sheet2!$B$1:$B$9,0))

Any ideas why this doesn't give me any result??

Thanks in advance for the help!
Jessica


--
sixpence668
------------------------------------------------------------------------
sixpence668's Profile: http://www.excelforum.com/member.php...o&userid=25472
View this thread: http://www.excelforum.com/showthread...hreadid=328525

Jessica,

I've written a "Bible Bingo" workbook that creates bingo
cards with the names of the books in the Bible.
It uses VBA programming code not formulas.
The code module is unlocked and viewable.

If you want a copy, I'll be glad to send it to you.
Send me a request via email (remove XXX from my email address).

Jim Cone
San Francisco, USA
XX


"sixpence668"

wrote in message

Paul B Wrote:
See if this will get you started, download bingo cards, almost at the
bottom
of the page
http://www.contextures.com/excelfiles.html
Paul B


I'm also looking to create some Bingo crads, and this file has been a
huge help! I've entered all of my words into the second sheet, and
it's creating one perfectly randomized Bingo card! Unfortunately, I
can't get it to produce _more_ than one. Even though the formulas are
the same as the ones in the sample file, all I get on the additional
cards is #NUM! for the results. As an example:

Here's the code for the first column on the original card -

=INDEX(Sheet2!$A$1:$A$9,MATCH(LARGE(Sheet2!$B$1:$B $9,ROW()-1),Sheet2!$B$1:$B$9,0))

Here's the code for the same column on the second card -

=INDEX(Sheet2!$A$1:$A$9,MATCH(LARGE(Sheet2!$B$1:$B $9,ROW()-3),Sheet2!$B$1:$B$9,0))

Any ideas why this doesn't give me any result??
Thanks in advance for the help!
Jessica
--
sixpence668