View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
JasonK[_3_] JasonK[_3_] is offline
external usenet poster
 
Posts: 38
Default creating variables

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