I need code to attach to a button!
In cell A1 I enter number of teams.
In Cell A2 I enter number of games to play.
Is there a way for me to click a button and...
In columns A and B it will give me a list of teams to play each other
based on the criteria above.
In Column A will be home team and B is Visitor.
Example I enter 9 teams with 12 games to play, hit the button and it will list the games in columns A and B.
If I need to explain more, I can.
Any help on this would be great!
Michael
If I knew the first thing about how to do this manually, I'm sure I could come up with the code. Are there rules about how teams can be matched, or is it just random? And where is your list of teams?
The list of teams can be just the numbers needed.
For example:
If I enter in A1 "9" (the number of teams) and in A2 "12" (the number of games).
Also, I did not clarify! Column A represents home games and Column B is Away games.
So....
Code would somehow come up with something like:
Column A Column B
1 2
3 4
5 6
7 8
9 1
2 8
3 7
etc. etc.
It would evenly match up the teams for home and away games up to 12 games.
Does this make more sense?
Michael
Just to clarify:
Is that kind of the league creator??
Number of games - is that total of games played by each team, number of times played against each opponent, number of times played against each opponent divided by two (one game at home, one away)?
Yes it IS!Is that kind of the league creator??
Yes, each team must play 12 games.Number of games - is that total of games played by each team
All I want here is to even out home and away in column A and Column B respectfully.number of times played against each opponent divided by two (one game at home, one away)?
Michael
How many teams will play in your league??
Is it always only 12 games (6 home / 6 away)
No, The value in A2 will reflect the outcome.
So if it comes out to 7 home and 6 away, that is fine. I just don't want something like 8 home and 5 away
Also, the number of teams will vary as well. The value in A1 will determine this.
Michael
Try to make them as even as possible
bump
I guess I need a sort of Do/Until it reaches the number of games.
Ideas?
Micahel
I'm working on it but it's pretty hard to find enough time before christmas
ok,
My appologies
ideas?
I tried this code with 4 teams in A1 and 12 games in A2:
But it does not even the teams games between Column A and Column B, also it doesn't stop it goes on forever.??Dim TEAM(10000) As Single With Range("A3:B290") .ClearContents End With Range("A3").Select MY_TEAMS = Range("A1").Value MY_GAMES = Range("A2").Value MY_TEAM = 1 MY_TEAM_2 = 2 Do Until MY_TEAM = MY_TEAMS And MY_TEAM_2 >= MY_TEAMS Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_TEAM Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = MY_TEAM_2 TEAM(MY_TEAM) = TEAM(MY_TEAM) + 1 TEAM(MY_TEAM_2) = TEAM(MY_TEAM_2) + 1 If MY_TEAM_2 = MY_TEAMS Then MY_TEAM_2 = MY_TEAM + 1 Else MY_TEAM_2 = MY_TEAM_2 + 1 End If If TEAM(MY_TEAM_2) = MY_TEAMS Then MY_TEAM_2 = MY_TEAM + 1 End If If TEAM(MY_TEAM) = MY_GAMES Then MY_TEAM = MY_TEAM + 1 MY_TEAM_2 = MY_TEAM + 1 End If If MY_TEAM_2 = MY_TEAM Then MY_TEAM_2 = MY_TEAM + 1 Loop
Ideas?
Michael
Ideas?
This is a demo of a permuations class that I wrote.
The button TEGWAR (The Exiciting Game Without Any Rules), randomly assigns players to teams and then schedules those teams against each other. The number of games is not determined by the user (per se) but by the number of games needed for each team to play each opponent once.
It's not the most efficient routine. Its designed to show off a generalized permutation class and generalized methods are not as efficient as specific ones.
To get a 12 team schedule, tell the first input box that 12 people want to play and tell the second box that there is 1 person per team. An 11 week round robin schedule will be produced.
Last edited by mikerickson; 01-15-2009 at 01:00 AM.
This is a more focused routine.
The notion is:
Consider a table with people sitting across from each other
A B C
D E F
if everyone shifts clockwise one seat, except for one person (F in this example)
D A B
E C F
and follows that pattern
E D A
C B F
C E D
B A F
B C E
A D F
eventualy everyone will have sat across the table from everyone else only once. (Played against everyone.)
Futhermore, everyone will have spent (approx) half their time on one side of the table and half on the other. (50% +/- home, 50% away).
(If F switches sides of the table each time, F also has the 50/50)
That's the approach of the attached routine.
Sheet 1 shows a spreadsheet approach. Each block of formulas is copy pasted to the right.
The button on Sheet 2 calls an input box which asks how many teams and a schedule is drawn. If there are an odd number of teams, a team named "bye" is added to the league.
This is a great start. I need them to have 12 games. I used your ScheduleTeamSS file, It only provided 8 games, is there something we can incorporate to give the 12 games?
Thank You for your response!!
Michael
Navigate to sheet 2, press the button and enter 12.
If you want the fixed team count spreadsheet version (sheet1) to be increased to 12, I can do that after work.
(He says as he plots a fiendishly un-editable way to make it variable sized rather than fixed team count
I misread your post. You want more games not more teams.
To do that, with Sheet1, copy F1:J8 and paste it, leaving one blank column between pasting blocks, into AD1, AJ1, AP1, etc.
Note: 12 teams with 12 games means that each team plays another team twice, but only one other team.
Posting Permissions
Reply With Quote
