Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating variables
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Variables Programmically | Excel Discussion (Misc queries) | |||
Creating dummy variables in Excel | Excel Worksheet Functions | |||
Creating dummy variables in Excel | Excel Worksheet Functions | |||
Creating a formula that will SUM with 2 Variables | Excel Worksheet Functions | |||
Creating sequential variables on the fly | Excel Programming |