I've written a small spreadsheet to keep track of a game that some
co-workers play.

At one point, when the spreadsheet is scoring, I need it to give me a
list of winners for that round. There are 11 people playing, so I
dimensioned 11 variables, "win1", "win2",..."win11" for the scoring
macro. When someone wins (and all 11 can win each round), the
variable "win1" will be assigned to the first winner's name, the
second to "win2" etc. I dimensioned 11 in case all 11 win.
It then prints to the screen a list of winners using msgbox.

My problem is, next year, we might have more or less than 11 players.
I want to be able to give the spreadsheet to anyone to use. They wont
be able to go in and change the code.

Is there a way to have any number of variables dimensioned to
accomodate any number of contestants?

Here are some pieces of the macro that scores.

Dim win1 As String
Dim win2 As String
Dim win3 As String
Dim win4 As String
Dim win5 As String
Dim win6 As String
Dim win7 As String
Dim win8 As String
Dim win9 As String
Dim win10 As String
Dim win11 As String


Count = Count + 1
If Count = 1 Then win1 = Cells(1, c.Column).Value
If Count = 2 Then win2 = Cells(1, c.Column).Value
If Count = 3 Then win3 = Cells(1, c.Column).Value
If Count = 4 Then win4 = Cells(1, c.Column).Value
If Count = 5 Then win5 = Cells(1, c.Column).Value
If Count = 6 Then win6 = Cells(1, c.Column).Value
If Count = 7 Then win7 = Cells(1, c.Column).Value
If Count = 8 Then win8 = Cells(1, c.Column).Value
If Count = 9 Then win9 = Cells(1, c.Column).Value
If Count = 10 Then win10 = Cells(1, c.Column).Value
If Count = 11 Then win11 = Cells(1, c.Column).Value

If Count 1 Then MsgBox Count & " contestants scored - " & win1 &
win2 & win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 1 Then MsgBox Count & " contestant scored - " & win1 & win2
& win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 0 Then MsgBox "nobody scored."

All of their names are on the first sheet in the workbook. I have a
cell that counts the contestants (A2).

Next year, if there are 15 contestants, I don't want to have to go
back and add more lines of code.

thanks for your help in advance.
JasonK

use an array

so say in Range("B1") is the number of players

SUB blah()
dim win() as string
players = range("B1").Value
redim win(1 to players)

then accumulating the wins is trivial, instead o fall those IF statements,
you need one line

Count = Count + 1
win(Count) = Cells(1, c.Column).Value



"JasonK" wrote in message
...

I've written a small spreadsheet to keep track of a game that some
co-workers play.

At one point, when the spreadsheet is scoring, I need it to give me a
list of winners for that round. There are 11 people playing, so I
dimensioned 11 variables, "win1", "win2",..."win11" for the scoring
macro. When someone wins (and all 11 can win each round), the
variable "win1" will be assigned to the first winner's name, the
second to "win2" etc. I dimensioned 11 in case all 11 win.
It then prints to the screen a list of winners using msgbox.

My problem is, next year, we might have more or less than 11 players.
I want to be able to give the spreadsheet to anyone to use. They wont
be able to go in and change the code.

Is there a way to have any number of variables dimensioned to
accomodate any number of contestants?

Here are some pieces of the macro that scores.

Dim win1 As String
Dim win2 As String
Dim win3 As String
Dim win4 As String
Dim win5 As String
Dim win6 As String
Dim win7 As String
Dim win8 As String
Dim win9 As String
Dim win10 As String
Dim win11 As String


Count = Count + 1
If Count = 1 Then win1 = Cells(1, c.Column).Value
If Count = 2 Then win2 = Cells(1, c.Column).Value
If Count = 3 Then win3 = Cells(1, c.Column).Value
If Count = 4 Then win4 = Cells(1, c.Column).Value
If Count = 5 Then win5 = Cells(1, c.Column).Value
If Count = 6 Then win6 = Cells(1, c.Column).Value
If Count = 7 Then win7 = Cells(1, c.Column).Value
If Count = 8 Then win8 = Cells(1, c.Column).Value
If Count = 9 Then win9 = Cells(1, c.Column).Value
If Count = 10 Then win10 = Cells(1, c.Column).Value
If Count = 11 Then win11 = Cells(1, c.Column).Value

If Count 1 Then MsgBox Count & " contestants scored - " & win1 &
win2 & win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 1 Then MsgBox Count & " contestant scored - " & win1 & win2
& win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 0 Then MsgBox "nobody scored."

All of their names are on the first sheet in the workbook. I have a
cell that counts the contestants (A2).

Next year, if there are 15 contestants, I don't want to have to go
back and add more lines of code.

thanks for your help in advance.
JasonK

Rather than trying to create one separate variable for each possible value, I
think you would benefit greatly from using arrays.

Here is some sample code to get you started; put a list of names in A1:A(x),
then you can run it

Private Function Find_LastCellInColumn(sht As Worksheet)
'find/up works in 2003, but unconfirmed for 2007- this searches down, so
may fail on empty cells
Find_LastCellInColumn = sht.Range("A1").End(xlDown).Row
End Function


Sub CreateListOfParticipants()

Dim RangeOfNames As Variant

LastRow = Find_LastCellInColumn(Sheet1)
RangeOfNames = Sheet1.Range("A1:A" & LastRow).Value

For i = LBound(RangeOfNames) To UBound(RangeOfNames)
if RangeOfNames(i, 1) <"" then FinalNameList = FinalNameList & chr(13)
& RangeOfNames(i, 1)
Next

msgbox FinalNameList

End Sub

The msgbox string has an extra chr(13) on the front end, but you can
manipulate the code as you see fit. You could either keep a second array for
the "win/lose" values, or make this array a 2D array and keep it all
together- up to you!

HTH
Keith

"JasonK" wrote:


I've written a small spreadsheet to keep track of a game that some
co-workers play.

At one point, when the spreadsheet is scoring, I need it to give me a
list of winners for that round. There are 11 people playing, so I
dimensioned 11 variables, "win1", "win2",..."win11" for the scoring
macro. When someone wins (and all 11 can win each round), the
variable "win1" will be assigned to the first winner's name, the
second to "win2" etc. I dimensioned 11 in case all 11 win.
It then prints to the screen a list of winners using msgbox.

My problem is, next year, we might have more or less than 11 players.
I want to be able to give the spreadsheet to anyone to use. They wont
be able to go in and change the code.

Is there a way to have any number of variables dimensioned to
accomodate any number of contestants?

Here are some pieces of the macro that scores.

Dim win1 As String
Dim win2 As String
Dim win3 As String
Dim win4 As String
Dim win5 As String
Dim win6 As String
Dim win7 As String
Dim win8 As String
Dim win9 As String
Dim win10 As String
Dim win11 As String


Count = Count + 1
If Count = 1 Then win1 = Cells(1, c.Column).Value
If Count = 2 Then win2 = Cells(1, c.Column).Value
If Count = 3 Then win3 = Cells(1, c.Column).Value
If Count = 4 Then win4 = Cells(1, c.Column).Value
If Count = 5 Then win5 = Cells(1, c.Column).Value
If Count = 6 Then win6 = Cells(1, c.Column).Value
If Count = 7 Then win7 = Cells(1, c.Column).Value
If Count = 8 Then win8 = Cells(1, c.Column).Value
If Count = 9 Then win9 = Cells(1, c.Column).Value
If Count = 10 Then win10 = Cells(1, c.Column).Value
If Count = 11 Then win11 = Cells(1, c.Column).Value

If Count 1 Then MsgBox Count & " contestants scored - " & win1 &
win2 & win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 1 Then MsgBox Count & " contestant scored - " & win1 & win2
& win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 0 Then MsgBox "nobody scored."

All of their names are on the first sheet in the workbook. I have a
cell that counts the contestants (A2).

Next year, if there are 15 contestants, I don't want to have to go
back and add more lines of code.

thanks for your help in advance.
JasonK

Patrick Molloy,
Thank you for the input. I haven't put it in place yet, but it makes
sense.
I wish this came easier for me.
thanks again,
JasonK


On Fri, 24 Jul 2009 21:59:44 +0100, "Patrick Molloy"
wrote:

use an array

so say in Range("B1") is the number of players

SUB blah()
dim win() as string
players = range("B1").Value
redim win(1 to players)

then accumulating the wins is trivial, instead o fall those IF statements,
you need one line

Count = Count + 1
win(Count) = Cells(1, c.Column).Value



"JasonK" wrote in message
.. .

I've written a small spreadsheet to keep track of a game that some
co-workers play.

At one point, when the spreadsheet is scoring, I need it to give me a
list of winners for that round. There are 11 people playing, so I
dimensioned 11 variables, "win1", "win2",..."win11" for the scoring
macro. When someone wins (and all 11 can win each round), the
variable "win1" will be assigned to the first winner's name, the
second to "win2" etc. I dimensioned 11 in case all 11 win.
It then prints to the screen a list of winners using msgbox.

My problem is, next year, we might have more or less than 11 players.
I want to be able to give the spreadsheet to anyone to use. They wont
be able to go in and change the code.

Is there a way to have any number of variables dimensioned to
accomodate any number of contestants?

Here are some pieces of the macro that scores.

Dim win1 As String
Dim win2 As String
Dim win3 As String
Dim win4 As String
Dim win5 As String
Dim win6 As String
Dim win7 As String
Dim win8 As String
Dim win9 As String
Dim win10 As String
Dim win11 As String


Count = Count + 1
If Count = 1 Then win1 = Cells(1, c.Column).Value
If Count = 2 Then win2 = Cells(1, c.Column).Value
If Count = 3 Then win3 = Cells(1, c.Column).Value
If Count = 4 Then win4 = Cells(1, c.Column).Value
If Count = 5 Then win5 = Cells(1, c.Column).Value
If Count = 6 Then win6 = Cells(1, c.Column).Value
If Count = 7 Then win7 = Cells(1, c.Column).Value
If Count = 8 Then win8 = Cells(1, c.Column).Value
If Count = 9 Then win9 = Cells(1, c.Column).Value
If Count = 10 Then win10 = Cells(1, c.Column).Value
If Count = 11 Then win11 = Cells(1, c.Column).Value

If Count 1 Then MsgBox Count & " contestants scored - " & win1 &
win2 & win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 1 Then MsgBox Count & " contestant scored - " & win1 & win2
& win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 0 Then MsgBox "nobody scored."

All of their names are on the first sheet in the workbook. I have a
cell that counts the contestants (A2).

Next year, if there are 15 contestants, I don't want to have to go
back and add more lines of code.

thanks for your help in advance.
JasonK

ker01,

thanks for help. someone else answered me as well, and I ended up
using his method to dimension the variables, and your method to print
the list of winners in a msgbox. works great after some tweaking.

thank you very much,
JasonK


On Fri, 24 Jul 2009 14:10:01 -0700, ker_01
wrote:

Rather than trying to create one separate variable for each possible value, I
think you would benefit greatly from using arrays.

Here is some sample code to get you started; put a list of names in A1:A(x),
then you can run it

Private Function Find_LastCellInColumn(sht As Worksheet)
'find/up works in 2003, but unconfirmed for 2007- this searches down, so
may fail on empty cells
Find_LastCellInColumn = sht.Range("A1").End(xlDown).Row
End Function


Sub CreateListOfParticipants()

Dim RangeOfNames As Variant

LastRow = Find_LastCellInColumn(Sheet1)
RangeOfNames = Sheet1.Range("A1:A" & LastRow).Value

For i = LBound(RangeOfNames) To UBound(RangeOfNames)
if RangeOfNames(i, 1) <"" then FinalNameList = FinalNameList & chr(13)
& RangeOfNames(i, 1)
Next

msgbox FinalNameList

End Sub

The msgbox string has an extra chr(13) on the front end, but you can
manipulate the code as you see fit. You could either keep a second array for
the "win/lose" values, or make this array a 2D array and keep it all
together- up to you!

HTH
Keith

"JasonK" wrote:


I've written a small spreadsheet to keep track of a game that some
co-workers play.

At one point, when the spreadsheet is scoring, I need it to give me a
list of winners for that round. There are 11 people playing, so I
dimensioned 11 variables, "win1", "win2",..."win11" for the scoring
macro. When someone wins (and all 11 can win each round), the
variable "win1" will be assigned to the first winner's name, the
second to "win2" etc. I dimensioned 11 in case all 11 win.
It then prints to the screen a list of winners using msgbox.

My problem is, next year, we might have more or less than 11 players.
I want to be able to give the spreadsheet to anyone to use. They wont
be able to go in and change the code.

Is there a way to have any number of variables dimensioned to
accomodate any number of contestants?

Here are some pieces of the macro that scores.

Dim win1 As String
Dim win2 As String
Dim win3 As String
Dim win4 As String
Dim win5 As String
Dim win6 As String
Dim win7 As String
Dim win8 As String
Dim win9 As String
Dim win10 As String
Dim win11 As String


Count = Count + 1
If Count = 1 Then win1 = Cells(1, c.Column).Value
If Count = 2 Then win2 = Cells(1, c.Column).Value
If Count = 3 Then win3 = Cells(1, c.Column).Value
If Count = 4 Then win4 = Cells(1, c.Column).Value
If Count = 5 Then win5 = Cells(1, c.Column).Value
If Count = 6 Then win6 = Cells(1, c.Column).Value
If Count = 7 Then win7 = Cells(1, c.Column).Value
If Count = 8 Then win8 = Cells(1, c.Column).Value
If Count = 9 Then win9 = Cells(1, c.Column).Value
If Count = 10 Then win10 = Cells(1, c.Column).Value
If Count = 11 Then win11 = Cells(1, c.Column).Value

If Count 1 Then MsgBox Count & " contestants scored - " & win1 &
win2 & win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 1 Then MsgBox Count & " contestant scored - " & win1 & win2
& win3 & win4 & win5 & win6 & win7 & win8 & win9 & win10 & win11

If Count = 0 Then MsgBox "nobody scored."

All of their names are on the first sheet in the workbook. I have a
cell that counts the contestants (A2).

Next year, if there are 15 contestants, I don't want to have to go
back and add more lines of code.

thanks for your help in advance.
JasonK