Set rng = Worksheets("Macro").Range("A2:A" & Lrow) Lrow = Worksheets("Macro").Range("A" & Rows.Count).End(xlUp).Row Go to sheet Macro and follow instructions.Īdd teams or players to column A, then press with left mouse button on the "Round-robin tournament" button or "Double round-robin tournament".Ī match schedule is created on a new sheet.Ĭonditional formatting separates rounds with a line, it makes the table easier to read. The macros in the workbook below allow you to create a match schedule. Here is a table that shows you teams play as many home as away games. 'Copy schedule and change home to away and vice versa, this makes it a double round-robin tournament 'Save half of the number of rows in array variable rngB to variable Val 'Name User Defined Function and specify parametersĭim res, rngB() As Variant, result As String, cc As LongĬc = (( + l) / 2) * ( + l - 1) The way it works is all play all twice, once home and once away. To make sure every team has as many home as away rounds competitors play each other twice. Double round-robin tournament - User Defined Function This table shows you how many times 6 teams play home and away for the entire tournament.ģ. This User Defined Function creates a random schedule split into rounds, home and away are also somewhat evenly distributed through the schedule. 'Return values in array variable tmp to worksheet 'switch places for all values except the first one 'Go from 1 to half of the number of rows in array variable rng2
'Save character - to the last container in array variable rngB 'Save value in array variable rngA to array variable rngB ReDim rngB(1 To UBound(rngA) + 1, 1 To 1) 'Continue here if the number of teams are not even 'Save cell values in variable rng to variable rngB 'This custom function adds a team automatically if the number of teams is uneven.ĭim tmp() As Variant, k As Long, l As Integerĭim i As Long, j As Long, a As Long, r As Longĭim rngA As Variant, Stemp As Variant, Val As Longĭim res, rngB() As Variant, result As String 'Name User Defined Function and specify parameters It tries to distribute home and away rounds evenly and teams are randomly placed in the schedule. This custom function creates a round-robin tournament. Round-robin tournament - distributes home and away rounds evenly The next custom function takes care of these three issues.Ģ. A team can't play twice in the same round, obviously.Īlso if you want the schedule to be somewhat random, this custom function is not for you. But if home and away doesn't matter this could useful.Īnother bad thing with this custom function is that it doesn't split the schedule into rounds. 'Continue with next number stored in variable iĪs you can see it is not very complicated and the first team has home matches all the time. 'Continue with next number stored in variable j 'Add 1 to the number stored in variable k and save to variable k 'Save value stored in worksheet based on variable j to array variable tmp based on variable k 'Save value stored in worksheet based on variable i to array variable tmp based on variable k 'Go from i + 1 to the number of cells in range variable rng 'Go from 1 to the number of cells in range variable rng
'Dimension variables and declare data types
ROUND ROBIN SCHEDULE CREATOR CODE
The following VBA code creates a schedule where each team plays once against another team. Basic scheduling - each team plays once against another team
ROUND ROBIN SCHEDULE CREATOR HOW TO
At the very end of this post are instructions on how to use the custom functions. You can use these custom functions below for creating a table for tennis, soccer, chess, bridge, or whatever sport/competition schedule you want. Excel is a great platform for building a round-robin tournament table and keeping scores.
0 kommentar(er)
