Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a fixture list
I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams - 90 matches). I have used the following macro. Thanks for your help in advance. Sub leagueMatch() Dim i, j As Long Dim datacolumn As String Dim resultColumn As String Dim firstRow, lastRow, currRow As Long datacolumn = "A" resultColumn = "C" firstRow = 1 currRow = 1 lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row For i = firstRow To lastRow - 1 For j = i + 1 To lastRow Range(resultColumn & Format(currRow)) = Range(datacolumn & Format(i)) & " vs " & Range(datacolumn & Format(j)) currRow = currRow + 1 Next Next End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a fixture list
Hi
This will give you 90 matches. Each team will play against each other twice (as host and as guest). Sub leagueMatch() Dim i, j As Long Dim datacolumn As String Dim resultColumn As String Dim firstRow, lastRow, currRow As Long datacolumn = "A" resultColumn = "C" firstRow = 1 currRow = 1 lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row For i = firstRow To lastRow For j = firstRow To lastRow If Cells(i, datacolumn) < Cells(j, datacolumn) Then Range(resultColumn & Format(currRow)) = Range(datacolumn & Format(i)) & " vs " & Range(datacolumn & Format(j)) currRow = currRow + 1 End If Next Next End Sub Regards, Per "Gilbo" skrev i meddelelsen ... I have used a macro to gererate fixtures for a league but it doesn't calculate properly. All teams should play each other once, (ie 10 teams - 90 matches). I have used the following macro. Thanks for your help in advance. Sub leagueMatch() Dim i, j As Long Dim datacolumn As String Dim resultColumn As String Dim firstRow, lastRow, currRow As Long datacolumn = "A" resultColumn = "C" firstRow = 1 currRow = 1 lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row For i = firstRow To lastRow - 1 For j = i + 1 To lastRow Range(resultColumn & Format(currRow)) = Range(datacolumn & Format(i)) & " vs " & Range(datacolumn & Format(j)) currRow = currRow + 1 Next Next End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a fixture list
Thanks, It does exactly what I want.
"Gilbo" wrote: I have used a macro to gererate fixtures for a league but it doesn't calculate properly. All teams should play each other once, (ie 10 teams - 90 matches). I have used the following macro. Thanks for your help in advance. Sub leagueMatch() Dim i, j As Long Dim datacolumn As String Dim resultColumn As String Dim firstRow, lastRow, currRow As Long datacolumn = "A" resultColumn = "C" firstRow = 1 currRow = 1 lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row For i = firstRow To lastRow - 1 For j = i + 1 To lastRow Range(resultColumn & Format(currRow)) = Range(datacolumn & Format(i)) & " vs " & Range(datacolumn & Format(j)) currRow = currRow + 1 Next Next End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a fixture list
Now you have work out the fixtures, would it be possible to work out the
matches for each week with another macro, ie 12 teams 6 matches Team1 v Team2 Team3 v Team4 Team5 v Team6 Team7 v Team8 Team9 v Team10 Team11 v Team12 So that every team plays every other team twice, and all teams play every week "Gilbo" wrote: Thanks, It does exactly what I want. "Gilbo" wrote: I have used a macro to gererate fixtures for a league but it doesn't calculate properly. All teams should play each other once, (ie 10 teams - 90 matches). I have used the following macro. Thanks for your help in advance. Sub leagueMatch() Dim i, j As Long Dim datacolumn As String Dim resultColumn As String Dim firstRow, lastRow, currRow As Long datacolumn = "A" resultColumn = "C" firstRow = 1 currRow = 1 lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row For i = firstRow To lastRow - 1 For j = i + 1 To lastRow Range(resultColumn & Format(currRow)) = Range(datacolumn & Format(i)) & " vs " & Range(datacolumn & Format(j)) currRow = currRow + 1 Next Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a fixture list | Excel Discussion (Misc queries) | |||
Random Grouping/Fixture List?? | Excel Worksheet Functions | |||
Fixture list in excel? | Excel Discussion (Misc queries) | |||
HOW DO I SET UP A 12 TEAM FIXTURE TABLE PLAYING EACH OTHER ONCE | Excel Discussion (Misc queries) | |||
Creating fixture lists | Excel Worksheet Functions |