First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which Id like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, Id like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? Id be
really grateful for any help.
Many thanks in advance, Neil Goldwasser

--

Dave Peterson