#21, Issue 77 - Excel code that creates a tournament spreadsheet template - Part 3; Adding the main menu code.
show: Excel coding to set up a tournament - Part 3
As we continue the coding for setting up a tournament spreadsheet, in this article we will start the "Create Template" menu heading coding. We will start off simply enough, defining a grid where the singles or doubles players will have their names inserted. The size depends on the 8, 16, 32 or 64 player selection and whether the names are listed in one or two columns according to the singles/doubles selection. For instance a doubles tournament with 16 players or an 8 player singles tournament will appear as follows: Selecting the "Create Template" menu item, as coded in the last two articles, runs the "FormatCode" macro. This is started off as follows:Code: Select all Sub FormatCode() Dim irow As Integer Dim icol As Integer Dim aplayer As String irow = 7 'starting row position icol = 3 'starting column position i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles)
The irow, icol matrix position on the spreadsheet is defined as the starting position of the grid. "i" records the number of players in cell position (3,2) - as defined by the other menu selections in the last 2 articles and "aplayer" whether this is a singles or doubles tournament. If it is a doubles tournament "k" is set to 1 from 0. The next code is as follows:Code: Select all For j = 0 To k 'One or two player rows Cells(irow, icol + j).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous 'Top border End With
The "For/Next" loop runs for 1 loop for singles or 2 loops for doubles as defined by the flag "k". the next "With/End With" loop is the rather long-winded method used to format a cell. Firstly the cell is defined, "Cells(irow, icol + j).Select" then the loop is used to define the type of formatting used; in this first case an border line at the top of the defined cell.Code: Select all Cells(irow, icol + j).Offset(i / (k + 1), 0).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous 'Bottom border End With
Now a bottom edge - note the offset addition is coded so that if it is a doubles tournament the lower line is half the number of players because there will be two columns for doubles tournaments.Code: Select all Cells(irow, icol + j).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous Selection.Font.Bold = True If aplayer = "Singles" Then ActiveCell.Value = "Players" Else If j = 0 Then ActiveCell.Value = "Player A" If j = 1 Then ActiveCell.Value = "Player B" End If End With
Next we put a lower line after the heading and include the heading as either "Players" or, for doubles, "Player A" with "Player B" in the next cell.Code: Select all For l = 0 To i / (k + 1) Cells(irow, icol + j).Offset(l, 0).Select If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l Cells(irow, icol + j).Offset(l, -1).HorizontalAlignment = xlRight With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous 'Left border End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous 'Right border End With Next l Next j End Sub
The last part of the routine is to define the edges of the grid and number the players, or doubles team numbers, down the left hand edge. The codes used are as follows: "i/(k+1)" is the number of players divided by 1 (for singles) or 2 (for doubles) to define the number of rows required. "If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l" states that if the counter is not 0 (i.e. not at the heading position) and it is the first pass through the two loop cycle then put "l" which is the player/team number in the defined cell, offset by -1 into the column before the defined cell. The full code is as follows:Code: Select all Sub FormatCode() Dim irow As Integer Dim icol As Integer Dim aplayer As String irow = 7 'starting row position icol = 3 'starting column position i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles) For j = 0 To k 'One or two player rows Cells(irow, icol + j).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous 'Top border End With Cells(irow, icol + j).Offset(i / (k + 1), 0).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous 'Bottom border End With Cells(irow, icol + j).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous Selection.Font.Bold = True If aplayer = "Singles" Then ActiveCell.Value = "Players" Else If j = 0 Then ActiveCell.Value = "Player A" If j = 1 Then ActiveCell.Value = "Player B" End If End With For l = 0 To i / (k + 1) Cells(irow, icol + j).Offset(l, 0).Select If l > 0 And j = 0 Then Cells(irow, icol + j).Offset(l, -1).Value = l Cells(irow, icol + j).Offset(l, -1).HorizontalAlignment = xlRight With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous 'Left border End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous 'Right border End With Next l Next j End Sub
Next article we will see about randomising the players names. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#22, Issue 78 - Excel code that creates a tournament spreadsheet template - Part 4; Randomising the player list.
show: Excel coding to set up a tournament - Part 4
As we continue the coding for setting up a tournament spreadsheet, in this article we will define the coding for randomising the list of players. Firstly we add a "Randomise" menu heading to our "Tournament" Excel menu bar that will run the code in the subroutine "RandomCode". The extra code for this, which is added to the subroutine "CreateMenu()", is as follows:Code: Select all ' Add a menu heading for randomising the list of players Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Randomise" cbcSubMenu.OnAction = "RandomCode" 'Randomise programme cbcSubMenu.FaceId = 1376 'assigns an icon
This adds to the existing menu, so that you get the following: The complete randomise subroutine is as follows:Code: Select all Sub RandomCode() Dim irow As Integer 'starting row Dim icol As Integer 'starting column irow = 7 'starting row position icol = 3 'starting column position 'check that the table exists A = Cells(irow, icol).Value 'First Heading If A = "" Then MsgBox "You need to select 'Create Template' first." Exit Sub Else 'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend 'check that the list is not already random If Cells(5, 2).Value = "Randomised" Then MsgBox "The player list has already been randomised" Exit Sub Else 'copy and paste the table Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy Cells(irow + j + 1, icol - 1).Select ActiveSheet.Paste 'copy in a random number For i = 1 To j - 1 Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()" Next i 'sort and clear the random number Range(Cells(irow + j + 2, icol), Cells(irow + j + i, icol + k + 1)).Select Selection.Sort Key1:=Cells(irow + j + 2, icol + k + 1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(Cells(irow + j + 2, icol + k + 1), Cells(irow + j + i, icol + k + 1)).Clear 'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With End If End If End Sub
There are a number of checks to make before randomising the list of players' names. Does the table exist?Code: Select all A = Cells(irow, icol).Value 'First Heading If A = "" Then MsgBox "You need to select 'Create Template' first." Exit Sub Else
Is the table full? Code: Select all 'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend
Has the list already been made random? Code: Select all 'check that the list is not already random If Cells(5, 2).Value = "Randomised" Then MsgBox "The player list has already been randomised" Exit Sub Else
If all these conditions are Ok then we copy the table and paste it below the existing table. Code: Select all 'copy and paste the table Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy Cells(irow + j + 1, icol - 1).Select ActiveSheet.Paste
Then we insert the Random Excel function. Code: Select all 'copy in a random number For i = 1 To j - 1 Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()" Next i
Then we sort by the random number and then "tidy up" by deleting the random numbers. Code: Select all 'sort and clear the random number Range(Cells(irow + j + 2, icol), Cells(irow + j + i, icol + k + 1)).Select Selection.Sort Key1:=Cells(irow + j + 2, icol + k + 1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(Cells(irow + j + 2, icol + k + 1), Cells(irow + j + i, icol + k + 1)).Clear
Finally we insert text that the list is now random - which also prevents the operation being carried out again. Code: Select all 'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With
The table now looks like the following (depending on your settings!): All the code for all subroutines is as follows. (Those of you follow the coding closely may spot that some parts of my previous publications have been simplified. That's because I too am still learning!)Code: Select all Sub CreateMenu() Dim cMenu1 As CommandBarControl Dim cbcCutomMenu As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcSubMenu As CommandBarControl Dim PlayerNumber As Integer '8, 16, 32 or 64 etc Dim GameType As String 'Singles or doubles etc Dim ColStart As Integer 'starting column number Dim Rowstart As Integer 'starting row number ' Put some default information in Cells.Select Selection.Font.Bold = False If Cells(3, 2).Value = "" Then Cells(2, 2).Value = "Tournament Settings:" Cells(3, 2).Value = "16" Cells(3, 3).Value = "Players" End If If Cells(4, 2).Value = "" Then Cells(4, 2).Value = "Singles" End If Cells(2, 2).Select Selection.Font.Bold = True On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpMenu = cbMainMenuBar.Controls("Help").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, before:=iHelpMenu) cbcCutomMenu.Caption = "Tourna&ment" Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) cbcSubMenu.Caption = "Number of Players" ' Set the '8' player menu heading With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "8" .OnAction = "Players8" If Cells(3, 2).Value = "8" Then .State = msoButtonDown Else .State = msoButtonUp End If End With ' Repeat for the 16, 32 and 64 player menu headings With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "16" .OnAction = "Players16" If Cells(3, 2).Value = "16" Then .State = msoButtonDown Else .State = msoButtonUp End If End With With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "32" .OnAction = "Players32" If Cells(3, 2).Value = "32" Then .State = msoButtonDown Else .State = msoButtonUp End If End With With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "64" .OnAction = "Players64" If Cells(3, 2).Value = "64" Then .State = msoButtonDown Else .State = msoButtonUp End If End With 'Add the second menu item that will lead off to another menu 'Set a CommandBarControl variable to it Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup) ' Give the control a caption cbcSubMenu.Caption = "Game Type" With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Singles" .OnAction = "TypeSingles" If Cells(4, 2).Value = "Singles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With With cbcSubMenu.Controls.Add(Type:=msoControlButton) .Caption = "Doubles" .OnAction = "TypeDoubles" If Cells(4, 2).Value = "Doubles" Then .State = msoButtonDown Else .State = msoButtonUp End If End With ' Add a menu heading for creating the template Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Create Template" cbcSubMenu.OnAction = "FormatCode" 'Main programme cbcSubMenu.FaceId = 625 'assigns an icon to the dropdown ' Add a menu heading for randomising the list of players Set cbcSubMenu = cbcCutomMenu.Controls.Add(Type:=msoControlButton) ' Give the control a caption cbcSubMenu.Caption = "Randomise" cbcSubMenu.OnAction = "RandomCode" 'Randomise programme cbcSubMenu.FaceId = 3817 'assigns an icon End Sub Sub FormatCode() Dim aplayer As String Dim irow As Integer 'starting row Dim icol As Integer 'starting column irow = 7 'starting row position icol = 3 'starting column position With Cells(2, 2) .Font.Bold = True .Value = "Tournament Settings:" End With Cells(3, 3).Value = "Players" i = Cells(3, 2).Value 'Number of players aplayer = Cells(4, 2).Value 'Type of game k = 0 'Flag = 0 for Singles) If aplayer = "Doubles" Then k = 1 'Flag = 1 for Doubles) i = i / 2 'Table is half size End If For j = 0 To k 'One or two player rows Cells(irow, icol + j).BorderAround _ ColorIndex:=0, Weight:=xlThin Range(Cells(irow, icol + j), Cells(irow + i, icol + j)).BorderAround _ ColorIndex:=0, Weight:=xlThin Next j For j = 1 To i Cells(irow + j, icol).Offset(0, -1).Value = j Next j If k = 0 Then 'Singles With Cells(irow, icol) .Value = "Players" .HorizontalAlignment = xlCenter .Font.Bold = True End With Else 'Doubles With Cells(irow, icol) .Value = "Player 1" .HorizontalAlignment = xlCenter .Font.Bold = True End With With Cells(irow, icol + 1) .Value = "Player 2" .HorizontalAlignment = xlCenter .Font.Bold = True End With End If End Sub Sub RandomCode() Dim irow As Integer 'starting row Dim icol As Integer 'starting column irow = 7 'starting row position icol = 3 'starting column position 'check that the table exists A = Cells(irow, icol).Value 'First Heading If A = "" Then MsgBox "You need to select 'Create Template' first." Exit Sub Else 'check that the table is full i = Cells(3, 2).Value 'Number of players k = 0 If Cells(4, 2).Value = "Doubles" Then i = i / 2 k = 1 End If j = 1 While j < i + 1 If Cells(irow + j, icol).Value = "" Then MsgBox "The player list is not complete" Exit Sub Else j = j + 1 End If Wend 'check that the list is not already random If Cells(5, 2).Value = "Randomised" Then MsgBox "The player list has already been randomised" Exit Sub Else 'copy and paste the table Range(Cells(irow, icol - 1), Cells(irow + j - 1, icol + k)).Copy Cells(irow + j + 1, icol - 1).Select ActiveSheet.Paste 'copy in a random number For i = 1 To j - 1 Cells(irow + j + i + 1, icol + k + 1).Value = "=RAND()" Next i 'sort and clear the random number Range(Cells(irow + j + 2, icol), Cells(irow + j + i, icol + k + 1)).Select Selection.Sort Key1:=Cells(irow + j + 2, icol + k + 1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range(Cells(irow + j + 2, icol + k + 1), Cells(irow + j + i, icol + k + 1)).Clear 'Show that the list is now random With Cells(irow + j + 1, icol - 1) .Value = "Random" .HorizontalAlignment = xlRight .Font.Bold = True End With With Cells(5, 2) .Value = "Randomised" .HorizontalAlignment = xlRight .Font.Bold = False End With End If End If End Sub Sub DeleteMenu() ' Use this if you get more than one "Tournament" menu item! On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("Tournament").Delete On Error GoTo 0 End Sub Sub Players8() Cells(3, 2).Value = "8" CreateMenu End Sub Sub Players16() Cells(3, 2).Value = "16" CreateMenu End Sub Sub Players32() Cells(3, 2).Value = "32" CreateMenu End Sub Sub Players64() Cells(3, 2).Value = "64" CreateMenu End Sub Sub TypeSingles() Cells(4, 2).Value = "Singles" CreateMenu End Sub Sub TypeDoubles() Cells(4, 2).Value = "Doubles" CreateMenu End Sub
(These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#23, Issue 79 - Use of VLookup function for assigning scores to a tournament spreadsheet.
show: Use of VLookup function for assigning scores.
As a break from the coding to set up a tournament, I'll be showing you the way in which a table of values can be "looked up". The coding discussed is code I use to transfer a set of scores from a lookup table and assign them to a player according to their final position in a tournament. The particular use is for updating the Tournament Players' Association scoresheet. Each tournament has its own sheet with the following layout: There is a list of the players' names in order of placement; their actual place and the routine that I will describe puts the scores in according to the placements. Note that some players share a position so must each have the same score which is the average of the total scores assigned to their different placements. Firstly we declare a few variables and assign some values;Code: Select all Sub Score() 'Name of subroutine Dim a As String Dim i As Integer Dim PlayerCol As Integer Dim ScoreCol As Integer PlayerCol = 1 'column number of players' names PositionCol = 3 'column number of final positions ScoreCol = 4 'column number to put scores in RowPos = 2 'first row used
Next we find out what type of tournament it is; either Standard, Premier or Major according to cell (1,5). The type decides what range of scores to use:Code: Select all atype = Cells(1, 5).Value If atype = "Major" Then TourneyType = 1 ElseIf atype = "Premier" Then TourneyType = 2 ElseIf atype = "Standard" Then TourneyType = 3 Else MsgBox "Tournament Type is out of permitted range!" Exit Sub End If
Note that 1, 2 or 3 are assigned to the "TourneyType" unless there is none of the three words in the correct position (row=1; column=5) in which case an error message is shown and the subroutine terminated. Now find out how many players are in the tournament by scanning down the list until a blank is found:Code: Select all 'itotal will be the total number of players taking part itotal = 0 a = Cells(RowPos + itotal, PlayerCol).Value While a <> "" itotal = itotal + 1 a = Cells(RowPos + itotal, PlayerCol).Value Wend Cells(1,6).Value =itotal
Now scan down the list of players' positions:Code: Select all 'scan down the column of positions For j = 1 To itotal k = Cells(RowPos + j - 1, PositionCol).Value If k > 33 Then k = 33
Note that k is equal to the player's position in row j and if k is larger than 33 then k is assigned to 33. This is because the score table is only 33 rows deep and all positions after 33 get the points as assigned to position 33. Now let's see the points table and how Vlookup works. This table is set up on a separate sheet with the name "Scoring. The table has to be defined firstly by highlighting the table and going to the menu Insert/Name/Define and enter the name of the table. You will see that the range of the table you highlighted appears in the range box. The next line of code is: l = WorksheetFunction.VLookup(k, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) OK so it looks complex, but think of is as VLookup(Row Value, Table, Position, Partial match?), the first value being the value of the row, which is k; then the table which is on sheet "Scoring" and from A2 to D32 in range; then the column position of the score which is one more than the TourneyType variable; then FALSE ensures an exact match otherwise it would be the nearest value. So we have variable (l) equal to the points for a given position according to one of three possible values in the table. Now we check to see what the next player's position is. If the next value is the same then we have to keep checking the following players positions to see if they also are the same - so that an average score has to be applied.Code: Select all m = 0 While Cells(RowPos + j + m, PositionCol).Value = k m = m + 1 l = l + WorksheetFunction.VLookup(k + m, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) Wend
"m" is used to count the number of players at position k. The "While-Wend" routine runs around itself until a different position is detected at which point m is equal to the number of players at the same position and l is the total of the scores in each position. The last bit is to put the score into the sheet:Code: Select all If m <> 0 Then l = Round(l / (m + 1), 0) For n = 0 To m Cells(RowPos + j - 1 + n, ScoreCol).Value = l Next n j = j + m Else Cells(RowPos + j - 1, ScoreCol).Value = l End If Next j End Sub
Either m = 0 to show that only one player has that position, or m is the number of players in the same position in which case all the scores relating to the different players (l) are divided by the number of players (m) and rounded up to an integer, then copied into all these players' positions. The total code is as follows:Code: Select all Sub Score() Dim a As String Dim i As Integer Dim PlayerCol As Integer Dim ScoreCol As Integer PlayerCol = 1 'column number of players' names PositionCol = 3 'column number of final positions ScoreCol = 4 'column number to put scores in RowPos = 2 'first row used atype = Cells(1, 5).Value If atype = "Major" Then TourneyType = 1 ElseIf atype = "Premier" Then TourneyType = 2 ElseIf atype = "Standard" Then TourneyType = 3 Else MsgBox "Tournament Type is out of permitted range!" Exit Sub End If 'itotal will be the total number of players taking part itotal = 0 a = Cells(RowPos + itotal, PlayerCol).Value While a <> "" itotal = itotal + 1 a = Cells(RowPos + itotal, PlayerCol).Value Wend 'scan down the column of positions For j = 1 To itotal k = Cells(RowPos + j - 1, PositionCol).Value If k > 33 Then k = 33 l = WorksheetFunction.VLookup(k, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) m = 0 While Cells(RowPos + j + m, PositionCol).Value = k m = m + 1 l = l + WorksheetFunction.VLookup(k + m, Sheets("Scoring").Range("A2:D34"), TourneyType + 1, False) Wend If m <> 0 Then l = Round(l / (m + 1), 0) For n = 0 To m Cells(RowPos + j - 1 + n, ScoreCol).Value = l Next n j = j + m Else Cells(RowPos + j - 1, ScoreCol).Value = l End If Next j End Sub
Next week will either be a continuation of setting up a tournament or the code I use in transferring these scores to a master spreadsheet of results. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#24, Issue 80 - TPA2 Scoring Spreadsheet - Adding an individual tournament score to a master spreadsheet.
show: TPA2 Scoring Spreadsheet.
Tournament Players Association Year 2 (TPA2) is a series of tournaments that goes on over a year. The first year there was one tournament released every week, this year I am helping to maintain the scoring for the 46 tournaments either completed or in progress and have used the spreadsheet that I will be describing in this article. Last issue's article described the routine used to update the individual tournament scores from the finishing positions. Each tournament has its own sheet, so the routine needs to do the following actions: * Input the tournament that you want to add its scores to the main spreadsheet scores; * Add the players names and their scores to the main spreadsheet; * Sort the spreadsheet by players names; * Check for duplicate names - if you fine a duplicate, add the scores to one and delete the other; * Sort by scores And that's it - basically. It is complicated by the fact that the three types of tournaments have their own sub totals and also that it is such a large spreadsheet that I have found some anomalies with the way in which Excel works. For instance, I like to maintain totals as a way of checking and have found that the formula used in Excel, SUM (A1:A300) say, needs to be restated as it can reduce in value even though the routine has not changed it. I therefore found that checksums were incorrect when the size of the spreadsheet increased beyond a certain point. OK. The spreadsheet looks like this:Click image to enlarge. This is sheet 1 of the spreadsheet. Sheets 2 to 48 hold the individual tournament results and sheet 49 is the Scoring sheet that I referred to last week and holds the table of possible scores for the three different types of tournament. We start, as usual, defining a few variables and assigning them values:Code: Select all Sub AddTourney() 'name of this routine Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String 'player's name Dim asheet As String 'sheet number irow = 7 'starting row position icol = 2 'starting column position iplayer = 0 itourneys = 52 '52 maximum tournaments isheet = 3 aplayer = "" addsheet = True 'this is a flag to erase a tournament score
Then we have a call to a subroutine to count the number of players:Code: Select all Call FindNumber(irow, icol, iplayers) 'find number of players
This subroutine takes the starting row and column position of the player list and also the starting number of players to the routine and is is coded as follows:Code: Select all Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate 'ensure you at the master sheet (called "Summary") iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" 'do this loop until we find a blank player name iplayers = iplayers + 1 'increment number of players aplayer = Cells(irow + iplayers, icol).Value 'get next player name Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) ' convert to lower case Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
The conversion to lower case is to minimise the chance of mistyping players names. We now want to find out which tournament is to be added to the master score sheet.Code: Select all 'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If
The ranking order is copied to the first column as this was found to change as the spreadsheet grew - without doing anything to it. Perhaps Excel 2007 gets over this? You enter the week number of the tournament to enter and you can also enter a negative number to erase a sheet's scores. Entering zero or a sheet higher than 52 or pressing cancel will exit the routine. The sheet is then activated so that you can get the information you need. Now we need to get the players names.Code: Select all i = 0 aplayer = Cells(i + 2, 1).Value 'player name If aplayer = "" Then 'no players' names in MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament
The sheet is checked to see if there is a players name in the first position. If not then there is an error message. If the cell in position (8, 1) has "Entered" in it then an error message is given and you return to the main spreadsheet screen. Otherwise the list of players is counted and copied as is the type of tournament. Note that if the scores are to be subtracted from the master spreadsheet than the scores are all changed to negative scores.Code: Select all Sheets("Summary").Select Cells(irow + iplayers, icol).Select ActiveSheet.Paste 'paste list of players below last one on master
It is a simple matter to paste in the copied list of players into the master sheet below the set of names already there.Code: Select all Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k
The tournament type is checked to be a valid one of the three types and there is a "1" or a "-1" put into the tournament number next to all the new players' names under the correct column for the tournament type.Code: Select all Sheets(asheet).Activate 'back to individual sheet Range(Cells(2, 4), Cells(1 + i, 4)).Select Selection.Copy 'copy score list Sheets("Summary").Select Cells(irow + iplayers, Val(B) + 9).Select ActiveSheet.Paste 'paste in next column Cells(irow + iplayers, icol + 3 + j).Select ActiveSheet.Paste 'paste
Back to the tournament page to copy the score. Returning to the master sheet this is pasted under the correct column for the tournament itself and also the column for the type of tournament.Code: Select all If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If
If the scores were negative than the routine returns to the individual sheet and turns them positive. "Entered" is copied to the sheet to inhibit repeated entry.Code: Select all Call FindNumber(irow, icol, iplayers) 'find number of players Rows(irow & ":" & irow + iplayers - 1).Select 'select the players rows Application.CutCopyMode = False Selection.Sort Key1:=Range("B" & icol), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'sort into alpha order
The new number of players is found by calling a subroutine and the whole set of rows copies and sorted by players names.Code: Select all For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then Cells(irow + k - 1, icol + j).Value = Cells(irow + k - 1, icol + j).Value + Cells(irow + k, icol + j).Value Cells(irow + k - 1, icol + j + 3).Value = Cells(irow + k - 1, icol + 3 + j).Value + Cells(irow + k, icol + 3 + j).Value For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k
The next block of code is to scan down the list of players' names and, if it finds two identical ones then each column value is added to the top row and then the bottom row deleted.Code: Select all Call FindNumber(irow, icol, iplayers) 'find number of players For k = 1 To iplayers Cells(irow + k - 1, 9).Value = Cells(irow + k - 1, 8).Value + Cells(irow + k - 1, 7).Value + Cells(irow + k - 1, 6).Value Next k Rows(irow & ":" & irow + iplayers - 1).Select Application.CutCopyMode = False Selection.Sort Key1:=Range("I" & icol), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
The number of players is then recalculated and the whole set of data resorted into score order.Code: Select all Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub
To finish off the niumber of players is copied down and put in a heading box and then a couple of formulas put in to act as checksums. The complete subroutines are as follows:Code: Select all Sub AddTourney() Dim irow As Integer 'row number for start Dim icol As Integer 'column number for start Dim isheet As Integer 'sheet stating number Dim iplayers As Integer 'number of players Dim itourneys As Integer 'number of tournaments Dim aplayer As String Dim asheet As String 'sheet number irow = 7 'starting row position icol = 2 'starting column position iplayer = 0 itourneys = 52 isheet = 3 aplayer = "" addsheet = True Call FindNumber(irow, icol, iplayers) 'find number of players 'Add a sheet For i = 1 To iplayers + 100 Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i ' which sheet? B = InputBox("Add a tournament's scores? Enter week number (or 0 to exit) Enter a negative week number to subtract the scores.") If B < 0 Then addsheet = False 'subtract that sheet B = -B End If If B <> "0" And B <> "" And B < itourneys Then 'b=week number asheet = "Week " & B 'go to Sheets(asheet).Activate Else Exit Sub 'invalid sheet number End If i = 0 aplayer = Cells(i + 2, 1).Value 'player name If aplayer = "" Then MsgBox ("Week " & B & " is not completed yet.") Sheets("Summary").Activate Exit Sub ElseIf Cells(8, 1).Value = "Entered" Then MsgBox ("Week " & B & "'s results have already been entered.") Sheets("Summary").Activate Exit Sub End If While aplayer <> "" i = i + 1 'i=number of players on the individual tourney sheet aplayer = Cells(i + 2, 1).Value If addsheet = False Then Cells(i + 1, 4).Value = -Cells(i + 1, 4).Value Wend Range(Cells(2, 1), Cells(2 + i - 1, 1)).Select Selection.Copy 'copy list of players atype = Cells(1, 5).Value 'type of tournament Sheets("Summary").Select Cells(irow + iplayers, icol).Select ActiveSheet.Paste 'paste list of players below last one on master Select Case atype Case Is = "Major" j = 1 Case Is = "Premier" j = 2 Case Is = "Standard" j = 3 Case Else MsgBox (atype & " is not permitted") Exit Sub End Select l = 1 If addsheet = False Then l = -1 For k = 1 To i Cells(irow + iplayers + k - 1, icol + j).Value = Cells(irow + iplayers + k - 1, icol + j).Value + l 'increment number of Tournaments Next k Sheets(asheet).Activate 'back to individual sheet Range(Cells(2, 4), Cells(1 + i, 4)).Select Selection.Copy 'copy score list Sheets("Summary").Select Cells(irow + iplayers, Val(B) + 9).Select ActiveSheet.Paste 'paste in next column Cells(irow + iplayers, icol + 3 + j).Select ActiveSheet.Paste 'paste If addsheet = False Then Sheets(asheet).Activate 'back to individual sheet Cells(8, 1).Value = "Entered" For k = 1 To i Cells(k + 1, 4).Value = -Cells(k + 1, 4).Value Next k Sheets("Summary").Select End If Call FindNumber(irow, icol, iplayers) 'find number of players Rows(irow & ":" & irow + iplayers - 1).Select 'select the players rows Application.CutCopyMode = False Selection.Sort Key1:=Range("B" & icol), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'sort into alpha order For k = 1 To iplayers - 1 aplayer = Cells(irow + k - 1, icol).Value aplayer2 = Cells(irow + k, icol).Value If aplayer = "" Then ElseIf aplayer = aplayer2 Then Cells(irow + k - 1, icol + j).Value = Cells(irow + k - 1, icol + j).Value + Cells(irow + k, icol + j).Value Cells(irow + k - 1, icol + j + 3).Value = Cells(irow + k - 1, icol + 3 + j).Value + Cells(irow + k, icol + 3 + j).Value For l = 10 To 10 + itourneys - 1 Cells(irow + k - 1, l).Value = Cells(irow + k - 1, l).Value + Cells(irow + k, l).Value If Cells(irow + k - 1, l).Value = 0 Then Cells(irow + k - 1, l).Value = "" Next l ActiveWindow.Panes(1).Activate Rows((irow + k) & ":" & (irow + k)).Select Selection.Delete Shift:=xlUp End If Next k Call FindNumber(irow, icol, iplayers) 'find number of players For k = 1 To iplayers Cells(irow + k - 1, 9).Value = Cells(irow + k - 1, 8).Value + Cells(irow + k - 1, 7).Value + Cells(irow + k - 1, 6).Value Next k Rows(irow & ":" & irow + iplayers - 1).Select Application.CutCopyMode = False Selection.Sort Key1:=Range("I" & icol), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Call FindNumber(irow, icol, iplayers) 'find number of players For i = 1 To iplayers Cells(irow + i - 1, 1).Value = i 'put ranking order in Next i Range("F4").Select ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[" & iplayers + 10 & "]C)" Range("F4:I4").Select Selection.FillRight Range("I6").Select ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & iplayers + 10 & "]C)" Range("I6:BD6").Select Selection.FillRight End Sub Sub FindNumber(irow, icol, iplayers) 'Find number of players on master sheet Sheets("Summary").Activate iplayers = 0 'number of players already aplayer = Cells(irow + iplayers, icol).Value While aplayer <> "" iplayers = iplayers + 1 aplayer = Cells(irow + iplayers, icol).Value Cells(irow + iplayers, icol).Value = StrConv(aplayer, vbLowerCase) Wend Cells(irow - 1, icol).Value = iplayers & " Players" End Sub
Phew! (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have Excel 2007)
#25, Issue 81 - Using Buttons - Getting to url's via buttons or pictures.
show: Using Buttons
From the very large programme in the last edition to a small article on the use of buttons in your tournament thread to either go to a different url or perform a search. By "button" I mean either a picture, possibly of a button, or a line of text. For instance; The scoreboard is at : Clicking on the picture of the button takes you to a spreadsheet. The code for this is quite simple (thanks to Dukasaur who explained it to me!):[url=X][img]Y[/img][/url] Where X is the address of the destination url and Y is the url of the picture. Let's perform a search now!Find All Round 2 Games Clicking on the text performs a search of games. Again the code is quite straight forward;[url=X]text[/url] "text" is the line of text that you want to use to initiate the search, however X in this case is the address of the conquer club search criteria, which in the above case is:http://www.conquerclub.com/player.php?submit=Search&gn=&gs=&np=&mp=&ty=&it=&po=&bc=&ft=&wf=&tw=&rl=&sg=&pt=&to=ContinentConquer%3A+World+Tour+%5BTPA2%5D&lb=Round+2&p1=&p2=&p3=&p4=&so=&page=1 Looks awesome, but if you look closely at this you will see the coded name of a tournament (ContinentConquer%3A+World+Tour+%5BTPA2%5D) and a Round number (Round+2) and a Page number (&page=1) and if you look closer still you will see the various names which actually refer to fields in the conquer club game finder function; i.e. "gn" = game number; "gs" is game list; "np" is number of players; "mp" is map; "ty" is type; "it" is initial troops; "po" is play order; etc down to p1, p2, p3, p4 which refer to the players 1 to 4. However, there are two easy ways to get this code (and thanks again, Duke, for the alternative one!). Either: [*]Perform the game finder search that you want to use (I have searched for Round 2 of the ContinentConquer tournament); [*]Pressing search brings up the games you wanted to search for; [*]Now "Right-Click" anywhere on the page and you bring up a menu; [*]Select "View Page Source" to open a page of coding for that page, which includes the search criteria; [*]On line 108 (this number may vary I believe) you find the line of code used for the search; this is gotoUrl = "player.php?submit=Search&gn=&gs=&np=&mp=&ty=&it=&po=&bc=&ft=&wf=&tw=&rl=&sg=&pt=&to=ContinentConquer%3A+World+Tour+%5BTPA2%5D&lb=Round+2&p1=&p2=&p3=&p4=&so=&page="; ; [*]Simply select the part of the code from "player.php" to "page="; [*]Add the address "http://www.conquerclub.com/" to the front and "1" to the end and you have your line of search criteria. Or; [*]Perform the search that you want to include; [*]Click to go to the next page; [*]The url in the address box is the code that you want, though pointing to page 2; [*]Change the last "2" to a "1" to point to page 1 and the code can be copied. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have later versions)
#26, Issue 82 - Tournament Rescue and Round Robins
show: Tournament Rescue and Round Robins.
As you probably have guessed, I get great satisfaction from using Excel in organising tournaments. However, I have not had any new/different tournament ideas recently so I looked at some of the tournaments that needed rescuing and picked up three that were part of the TPA series of tournaments. You quickly realise just how inventive others are in their tournament design and it certainly made me think about different ways of managing them using Excel. As an example there is one that started with 100 players playing 100 games on 100 different maps - fantastic! At the stage that I picked it up the remaining 39 players needed to complete 47 more sets of games. 38 of these is by every player playing everyone else - a Round Robin of 39 players. To do any round robin, irrespective of the number of players there is a simple way of designing it. For instance, with 14 players the match up in Round 1 would be:1 vs 14 2 vs 13 3 vs 12 4 vs 11 5 vs 10 6 vs 9 7 vs 8 For the remaining rounds you leave player 1 in first place and "rotate" the rest of the players. i.e.Round 2 1 vs 13 14 vs 12 2 vs 11 3 vs 10 4 vs 9 5 vs 8 6 vs 7 and so on;Round 3 1 vs 12 13 vs11 14 vs 10 2 vs 9 3 vs 8 4 vs 7 5 vs 6 To translate these into sets of players, the numbers are written into the spreadsheet in two columns and the second set can be coded by referencing the appropriate position in the first set (according to the above sequence). When all positions in the second set are complete, the whole table can be copied sequentailly to complete the number of rounds. Part of the table therefore looks like: The round numbers are on top. (The players reference numbers are not sequentially as they are from a previous list that was not ordered alphabetically) The maths for the round robin types of tournament are as follows. For n players , if n is even, there will be n-1 rounds with n/2 games each round (for 1 vs 1 type games of course!). If n is odd then you have to use(n+1) as the "n" value. OK, so for my tournament I have 38 rounds and, in my spreadsheet, I have a table of 38 sets of two columns of the 39 player match-ups. I have to assign players' names to this and, as in previous articles, I use the Excel VLOOKUP function. Firstly all the 39 players' names are in a vertical column with 1-39 copied next to them. I highlight the table and, using Insert/Name/Define, I define them as a table called, say, "Players". In the columns next to my two sets of numbers I use Vlookup to assign the appropriate player name - as "=VLOOKUP(player number position, Players, 2, FALSE)" which brings the player name from column 2 of the table "Players" where the number in column 1 matches exactly the player number position specified in my list of match-ups. I copy the function to every row and so have a double list of players names matching the numbers. I can then create the 19 games and list their numbers next to the two players names. For instance I have 1___Player Number 1_____Player Number 20____20____Game Number in 5 columns on my spreadsheet. This looks like (for Round 64 on Europe 1914 map: With 39 players, of course the odd number means that the round robin is designed for 40 players and each turn a different player will miss a game. For each round, 19 games are created and the first challenge is to get 39 players assigned to their correct game. The easiest way is to send a list of all the games to all the players involved and allow them to join their correct game. This may be difficult if you have novices or player who do not look at their private messages very frequently, though I expect that a tournasment that has already had 53 rounds would, by now, only contain the battle-hardened tournament professionals! In the next column on my spreadsheet I therefore create the joining information for the two players and the game number and the "to" CC function. The code is as follows:Code: Select all "Round "&CELL_WITH_ROUND_NUMBER&"; "&PLAYER1_POSITION&" vs "PLAYER2_POSITION"&" [to]"GAME_NUMBER_POSITION&" PASSWORD&"[/to]"
which creates the following when pm'ed to the players;Code: Select all Round 54; Player 1 vs Player 2 View Game 123456 (Auto-Join Game)
All I need to do is to add a message for the one person who will not get a game due to the odd number of players. To do this I use an IF function; IF(condition, Do this if correct, Otherwise do this) i.e. IF(GameNumber<>"",Player game information as above,"Player X does not have a game this round"). The condition is that the game number ="" (i.e. blank) This looks like the following:Code: Select all =IF(I73="",G73&H73&" has no game this round","Round "&B$48&"; "&G73&" vs "&H73&" [to]"&I73&" R"&B$48&"[/to]")
I also have a set of diferent codes to paste a table of match-ups into the tournament thread which is:Code: Select all Player 1 vs Player 2 [game]11702170[/game]
and looks like: Player 1 vs Player 2 Game 11702170 . This is used to publish the information to the players to keep them informed every round without taking up much time to do. I think it is always important to publish as much information for your players as possible, but if you can do it with a few lines of Excel code, all the better! In the next issue I will show you how to automate the above sequence. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#27, Issue 83 - Excel code to automate the game information for a round robin tournament.
show: Automating a Round Robin Tournament.
In the last issue I showed how a spreadsheet of match-ups can be used to make the invites and tournament thread information easier to manage. In this one I will describe a macro to automate the creation of this information. The round robin matchups were in tables (as described in the last issue): The round number is above a table of the 1 vs 1 player number match-ups. Once one table is created (as described in the last issue) it is simply copied sequentially as many times as required to complete the round robin series. This spreadsheet is called "Round Robins" and a different spreadsheet ("Games") has the detailed game information with the VLookup function to assign the player names to their respective numbers. This spreadsheet is as follows: Note that there are two blanks in the table. This is where two players ave dropped out and, since this particular tournament was in its final stages, they are not being replaced. The blanks mean that the opponent has a bye that round. Also, at the end of the tournament all players who played a dropped out player and won must have their total scores reduced by one. Once one table is created, the following macro can be used to create the next one. The descriptions of the stages are described in the coding:Code: Select all Sub AddRound() ' select the "Games" spreadsheet Sheets("Games").Select 'select the row number of the last table's map reference number cell - this we will call LineStart LineStart = InputBox(Prompt:="Last Row Number", _ Title:="Enter the last row number", Default:="Enter Previous Row Number of the Round Number") 'select the map number in column "B" and Referenced Map in column "C" Range("B" & LineStart, "C" & LineStart).Select Selection.Copy 'add 23 to LineStart (depth of the table) for a variable we can call LineDest linedest = LineStart + 23 'Select and paste the two values to the destination cell Range("B" & linedest, "C" & linedest).Select ActiveSheet.Paste 'get the previous round number and increment it iRoundNumber = Cells(linedest, 2).Value + 1 'paste this value into the destination cell - the VLookup function that we pasted next to it will automatically update the map to the new value Cells(linedest, 2).Value = iRoundNumber 'select the Round Robin sheet Sheets("Round Robin").Select 'Look for the next round number (starting at column 8 and incremented by 3 for the next table I = 8 While Cells(1, I).Value <> iRoundNumber I = I + 3 Wend 'select the table below this round number Range(Cells(3, I), Cells(22, I + 1)).Select Selection.Copy ' go back to the games spreadsheet Sheets("Games").Select 'select the next table start position Cells(linedest + 2, 2).Select 'paste the values into the table - not the formulae! - using paste special. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Move the second column; selecting it first.... Range(Cells(linedest + 2, 3), Cells(linedest + 21, 3)).Select '...cutting it... Selection.Cut '...selecting the new position for it... Cells(linedest + 2, 5).Select '...and pasting it there ActiveSheet.Paste 'select the VLookup function from the previous table... Range(Cells(linedest - 2, 3), Cells(linedest - 2, 4)).Select 'copy it.. Selection.Copy 'and paste in the first row of the second table Cells(linedest + 2, 3).Select ActiveSheet.Paste 'select the whole table and copy the functions down Range(Cells(linedest + 2, 3), Cells(linedest + 21, 4)).Select Selection.FillDown 'copy this table... Selection.Copy 'and paste alongside - this is pasting the values not the formulae - for safety in case of changes to the player list Cells(linedest + 2, 7).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'search for any "0" values and replace them with blanks For I = 1 To 19 If Cells(linedest + 1 + I, 7) = "0" Then Cells(linedest + 1 + I, 7) = "" If Cells(linedest + 1 + I, 8) = "0" Then Cells(linedest + 1 + I, 8) = "" Next I 'Now paste in the formula for allowing players to join - this is complex! a = "=IF(I" & (linedest + 2) & "="""",G" & (linedest + 2) a = a & "&H" & (linedest + 2) a = a & "&"" has no game this round.""," & "" a = a & """Round ""&B$" & linedest & "&""; ""&G" & (linedest + 2) a = a & "&"" vs ""&H" & (linedest + 2) & "& "" [to]""&I" a = a & (linedest + 2) & "&"" R""&B$" & (linedest) & "&""[/to]"")" Cells(linedest + 2, 10).Value = a 'Paste the thread formula into the next column a = "=IF(I" & (linedest + 2) & "="""",G" & (linedest + 2) a = a & "&H" & (linedest + 2) a = a & "&"" has no game this round.""," & "" a = a & """Round ""&B$" & linedest & "&""; ""&G" & (linedest + 2) a = a & "&"" vs ""&H" & (linedest + 2) & "& "" [game]""&I" a = a & (linedest + 2) & "&""[/game]"")" Cells(linedest + 2, 11).Value = a 'copy down the two formulae Range(Cells(linedest + 2, 10), Cells(linedest + 21, 11)).Select Selection.FillDown 'Add the headers that can go into the invites and tournament thread Cells(linedest, 10).Value = "Round " & Cells(linedest, 2).Value & " is on map " & Cells(linedest, 3).Value & ". Join just 1. 2 players have byes." Cells(linedest, 11).Value = "Round " & Cells(linedest, 2).Value & " is on map " & Cells(linedest, 3).Value & ". 2 players have byes." End Sub
To use a new block of code a pm is sent to all the players in the tournament with the block of data below column 10. This can be used for the players to join their correct game. The block of data in column 11 can be written into the tournament thread to track the rounds and provide access information. In principle, any required sequences of operations can be written into Excel using the Record Macro function. So, even if you don't know or fully understand the above coding, you can still use this recording function to automate a sequence of Excel operations. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#28, Issue 84 - Excel code for extracting the order of terminations.
show: Terminator Tournaments
Some tournaments have points awarded according to the order that players are terminated. It can therefore be a laborious process to enter each game and note the orders so, even though it requires you still to enter each game, I'll show you a method of speeding up getting the results. On each terminator game is the list of termination details: The text from this list can be copied and pasted into a spreadsheet and if you look closely at the important termination details, say "2011-10-28 02:32:44 - JJ41375 eliminated morleyjoe from the game in round - 6" you can pick out the two player names as the first is after "- " and the second after "terminated". There is a useful function in Excel that can find text, so =FIND("- ",C3,1) will look for the "- " characters in cell C3 which in this case holds the line of text from the game details. This comes back with "21" which is the character position of the "- " text. Similarly =FIND("eliminated ",C3,1) will come back with character position 31. We can therefore pick out the first player's name with the code =MID(C3,E3+2,F3-E3-2) which takes the middle text out of cell C3 starting at 21 plus 2 (which is the start position of the player's name) and the number of characters in the player's name is 31-21-2 i.e. start position of "terminated" less the start position of "- " less 2. The formula to get the second player's name is =MID(C3,F3+11,FIND(" from",C3,F3)-F3-10) which uses the Find function again to get to the end of the second player's names. So, we can get the two names in two columns of the spreadsheet using Excel functions. We now need to use a bit of Visual Basic to get rid of the two lines of text regarding points (unless your tournament uses these values of course!) so that we get the relevant lines of termination details.Code: Select all 21 31 JJ41375 morleyjoe 21 32 redbugal callmecommander 21 34 GreenBaize redbugal 21 34 GreenBaize koontz1973 21 34 GreenBaize JJ41375
This gives the terminations in the order they occurred and there are blank cells next to the names into which the Visual Basic code can put "scores" according to the tournament rules. i.e.Code: Select all 21 31 JJ41375 1 morleyjoe 1 21 32 redbugal 1 callmecommander 2 21 34 GreenBaize 1 redbugal 3 21 34 GreenBaize 1 koontz1973 4 21 34 GreenBaize 9 JJ41375 6
In this tournament's rules (Sonic goes back to school) there is a point per termination; and extra 8 points for the winner and points that increase in the order of terminations - as shown next to the player's names. So, on to the coding used. Firstly let me show you the finished page of results, which gives you an idea of what the required outputs are:Click image to enlarge. This has the game number on the left and the termination data for each game next to it. On the right is a summary list of all the totals in the order of scores and the far right column is a list of the unique terminations which was required in this tournament as there is a second medal for its leader. The two buttons trigger the two sets of code; the first "Rationalise" extracts the termination data and ascribes the scores and the second "Total" summarises the two columns of scores and terminations. The coding for the two buttons is as follows (with descriptions within the code):Code: Select all Sub Terminator() Dim ListEnd As Integer Dim ListStart As Integer 'Find the end of the list ListEnd = 3 While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend 'Find the start of the last set of data ListStart = ListEnd While Cells(ListStart, 2).Value = "" ListStart = ListStart - 1 Wend ' Delete the non-relevant information i = ListStart While ListEnd <> i Rows(i + 1 & ":" & i + 1).Select Selection.Delete Shift:=xlUp Selection.Delete Shift:=xlUp ListEnd = ListEnd - 2 i = i + 1 Wend ListEnd = ListEnd - 1 'Select and paste down the Excel functions Range(Cells(ListStart - 1, 5), Cells(ListEnd, 9)).Select Selection.FillDown 'put in the values required by the tournament j = 1 For i = ListStart To ListEnd Cells(i, 10).Value = j j = j + 1 Cells(i, 8).Value = 1 Next i Cells(i - 1, 8).Value = 9 End Sub
Note that this code will not work properly if a player is thrown out for missing his goes, as there are extra lines of non-relevant text in the list that have to removed manually before the code can be run. The Total code is:Code: Select all Sub Total() Dim ListEnd As Integer Dim ListStart As Integer ListEnd = 3 ListStart = 3 'Find the last cell used While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend ListEnd = ListEnd - 1 'Select the first two columns Range(Cells(ListStart, 7), Cells(ListEnd, 8)).Select Selection.Copy 'Paste special into the results column Range(Cells(ListStart, 12), Cells(ListEnd, 13)).Select Selection.PasteSpecial Paste:=xlPasteValues 'Also paste into the list of terminations Range(Cells(ListStart, 15), Cells(ListEnd, 16)).Select Selection.PasteSpecial Paste:=xlPasteValues 'Put in just 1 termination For i = 3 To ListEnd Cells(i, 16) = 1 Next i 'Select the second set of columns Range(Cells(ListStart, 9), Cells(ListEnd, 10)).Select Selection.Copy 'Paste on the end of the first set of columns Range(Cells(ListEnd + 1, 12), Cells(ListEnd + ListEnd + 1, 13)).Select Selection.PasteSpecial Paste:=xlPasteValues 'Select and sort by name Range(Cells(ListStart, 12), Cells(ListEnd + ListEnd + 1, 13)).Select Selection.Sort Key1:=Range("L3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Select and sort the second set of columns Range(Cells(ListStart, 15), Cells(ListEnd, 16)).Select Selection.Sort Key1:=Range("O3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Check for names the same - if so delete and add on the scores For i = 3 To ListEnd + ListEnd + 1 a = Cells(i, 12).Value b = Cells(i + 1, 12).Value If b = a Then Cells(i + 1, 13).Value = Cells(i + 1, 13).Value + Cells(i, 13).Value Cells(i, 12).Value = "" Cells(i, 13).Value = "" End If Next i 'Sort by score Range(Cells(ListStart, 12), Cells(ListEnd + ListEnd + 1, 13)).Select Selection.Sort Key1:=Range("M3"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Do the same for the second list 'Check for names the same - if so delete and add on the scores For i = 3 To ListEnd a = Cells(i, 15).Value b = Cells(i + 1, 15).Value If b = a Then Cells(i + 1, 16).Value = Cells(i + 1, 16).Value + Cells(i, 16).Value Cells(i, 15).Value = "" Cells(i, 16).Value = "" End If Next i 'Sort by score Range(Cells(ListStart, 15), Cells(ListEnd + ListEnd + 1, 16)).Select Selection.Sort Key1:=Range("P3"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub AddAll() Dim ListEnd As Integer Dim ListStart As Integer ListEnd = 2 ListStart = 2 'Find the last cell used While Cells(ListEnd, 3).Value <> "" ListEnd = ListEnd + 1 Wend ListEnd = ListEnd - 1 'Select and sort by name Range(Cells(ListStart, 2), Cells(ListEnd + ListEnd + 1, 3)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Select and sort the second set of columns Range(Cells(ListStart, 5), Cells(ListEnd, 6)).Select Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Check for names the same - if so delete and add on the scores For i = 2 To ListEnd + ListEnd + 1 a = Cells(i, 2).Value b = Cells(i + 1, 2).Value If b = a Then Cells(i + 1, 3).Value = Cells(i + 1, 3).Value + Cells(i, 3).Value Cells(i, 2).Value = "" Cells(i, 3).Value = "" End If Next i 'Sort by score Range(Cells(ListStart, 2), Cells(ListEnd + ListEnd + 1, 3)).Select Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Do the same for the second list 'Check for names the same - if so delete and add on the scores For i = 2 To ListEnd a = Cells(i, 5).Value b = Cells(i + 1, 5).Value If b = a Then Cells(i + 1, 6).Value = Cells(i + 1, 6).Value + Cells(i, 6).Value Cells(i, 5).Value = "" Cells(i, 6).Value = "" End If Next i 'Sort by score Range(Cells(ListStart, 5), Cells(ListEnd + ListEnd + 1, 6)).Select Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
What is then required for this particular tournament is to add all the scores together from the different "subject" sheets and publish them in the tournament thread. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#29, Issue 85 - Excel code used to add tournament result points to the TPA2 master spreadsheet.
show: TPA2 Coding to Modify the Results Spreadsheet
In newsletter #80 I detailed the results spreadsheet for TPA2 scores and coding to update it with an individual tournament results. This does not, however cope with the change that was introduced after TPA1 to select the best scores from a number of scores. For instance there are only 8 Premier tournament scores allowed from the 13 actual premier tournaments and only 16 from the 28 Standard tournaments. The problem with doing this within the main coding system is that the checksums would be compromised, so I have decided to do this using an additional routine that is accessed from a button on the main spreadsheet. The process of working out the coding follow the same sequence that you would do manually; for instance: [*]Check each player in turn; [*]Check the number of Premier and Standard tournaments completed against the allowed number; [*]If the number is exceeded then get all the tournament scores of that particular type; [*]Sort them into order; [*]Take away the sum of all the lowest scores above the maximum number allowed from the total. Done! In doing the coding it is straight forward until we get to the sorting. For instance (with description within the coding);Code: Select all Sub CheckNumber() 'set variables iPlayerRow = 7 'First Player Row iListStart = 58 'Row used for lists iPlayerTotal = 0 'Total of Number of Players 'I decided to copy the players names and totals in blank columns after the main spreadsheet, hence the starting position of column 58 'Copy Names and Totals into blank columns - also getting the total number of competitors While Cells(iPlayerRow + iPlayerTotal, 2).Value <> "" Cells(iPlayerRow + iPlayerTotal, iListStart).Value = Cells(iPlayerRow + iPlayerTotal, 2).Value Cells(iPlayerRow + iPlayerTotal, iListStart + 1).Value = Cells(iPlayerRow + iPlayerTotal, 9).Value iPlayerTotal = iPlayerTotal + 1 Wend 'Select Premier and Standard Columns, icol being th enumber of these column totals For icol = 4 To 5 'Select the Maximum Number of Tournaments and call it iMax iMax = Cells(2, icol).Value 'Scan down the columns to see if the maximum number has been exceeded For irow = iPlayerRow To iPlayerRow + iPlayerTotal If Cells(irow, icol).Value > iMax Then 'maximum number exceeded 'put this number into the column next to the palyers' names Cells(irow, iListStart + icol - 2).Value = Cells(irow, icol).Value
We are now at the position of getting the scores and putting them into a table or array. I would normally use an array, but sorting is easier in excel, so I decided to use 1 table of 30 rows (higher than the 28 max standard tournaments within the spreadsheet itself). This has the added advantage of being easier to check when testing;Code: Select all 'clear table For i = 1 To 30 Cells(iPlayerRow + i, iListStart + 4).Value = 0 Next i 'Scan across all 47 tournaments For i = 10 To 56 'Check that the tournament type matches either Standard or Premier If (Cells(4, i).Value = "Premier" And icol = 4) Or (Cells(4, i).Value = "Standard" And icol = 5) Then 'Check that there is a score from this tournament If Cells(irow, i).Value > 0 Then 'If so, find next blank position in table and enter the score j = 1 While Cells(iPlayerRow + j, iListStart + 4).Value <> 0 j = j + 1 Wend 'insert the value into the table Cells(iPlayerRow + j, iListStart + 4).Value = Cells(irow, i).Value End If End If Next i
So far so good! We can sort using the Excel sorting system. An easy way of getting the coding is to manually create a Macro, select the table and sort. Then look at the coding used by the macro and simply copy it into your programme. After sorting in reducing order we sum the scores above the permitted maximum and subtract them from the total score - this coding was taken from the Internet and I still do not understand the format!Code: Select all 'Sort High to low 'This coding is straight from the macro created by Excel Range(Cells(iPlayerRow + 1, iListStart + 4), Cells(iPlayerRow + 30, iListStart + 4)).Select Selection.Sort Key1:=Range("BJ8"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Reduce the total by the sum of values below the value that is the iMax number 'Coding taken from the Internet! Range("BJ7").Formula = "=SUM(" & Range(Cells(iPlayerRow + 1 + iMax, iListStart + 4), Cells(iPlayerRow + 30, iListStart + 4)).Address(False, False) & ")" 'Reduce the total and adjust the number of tournaments to iMax Cells(irow, iListStart + 1).Value = Cells(irow, iListStart + 1).Value - BJ7 Cells(irow, iListStart + 2).Value = iMax End If Next irow Next icol End Sub
And that is it. I access this routine from a button on the spreadsheet. To put in a button, see my archive Archive and check out number 10 in my series (Newsletter #66). (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)
#30, Issue 86 - Finding a game without using Game Finder and what the various labels in refer to.
show: Secrets of Game Finder
If you want to insert a link to a selection of games, you use the code that I detailed in Issue 81. For instance:Find All Round 2 Games This code looks like:Code: Select all [url=http://www.conquerclub.com/player.php?submit=Search&gn=&gs=&np=&mp=&ty=&it=&po=&bc=&ft=&wf=&tw=&rl=&sg=&pt=1&to=&lb=&p1=&p2=&p3=&p4=&so=&page=1][b]Find All Round 2 Games[/b][/url]
In this code there are lots of variables such as "gn=" and only a few are set to a value. It looks quite horrendous, but if you compare it with the Game Finder screen, you will see that it starts to make sense. Click image to enlarge. The "&" is to link each of the variables together. Let's define what these variables are: gn= G ame N umber gs= G ame S tatus (W=Waiting; A=Active; F=Finished} np= N umber of P layers (2, 3 etc) mp= M ap (Map Number; not Name!) ty= Game Ty pe (S (or blank) =Standard; C=Terminator; A=Assassin; D=Doubles; T=Triples; Q=Quads) it= I nitial T roops (M=Manual) po= P lay O rder (F=Freestyle) bc= Type of Spoils (1 (or blank)=No Spoils; 2= Escalating; 3=Flat Rate; 4 = Nuclear) ft= Reinf orcement T ype (O for Adjacent; M=Unlimited; C=Chained)) wf= W ith F og (Y = yes) tw= T rench W arfare (Y = yes) rl= R ound L imit (20 etc) sg= S pecial G amesplay (5 etc) pt= P rivat e (y = Yes; T = Tournament) to=To urnament Title i.e.ContinentConquer%3A+World+Tour+%5BTPA2%5D lb= Game L ab el i.e. Round+2 p1= P layer 1 p2= P layer 2 p3= P layer 3 p4= P layer 4 so=???? page= Page Number Perhaps "so" is for a variable that is yet to be specified? Does anyone know? (Please note that the above letters in red are my interpretation of their derivation!) Just a quick note on the Tournament Title, which codes "ContinentConquer: World Tour [TPA2]" as ContinentConquer%3A+World+Tour+%5BTPA2%5D. The %3A represents the colon; + represents a space and %5B and %5D represent "[" and "]". If "[" and/or "]" were used instead then they interfere with the coding which results in an error message. This is the reason that using Game Finder followed by page forward and page back and getting the resulting code from the thread address does not work if either of these characters is in the title. (These "tips" are ones that I have tried to make it a bit easier in managing my own tournaments. I am sure there are different and better methods than these, so please let me know. Any queries on the above I'll be glad to respond to! Please note that I use Excel 2003, so there may be variation in the menu instructions if you have other versions of Excel)