Hi,
I have written a macro
The purpose is to caculate all 4499879580584837311451522624 combinations
(14 x 14 x 14 x 14...14 (14^25)
However, I realize with my computer's speed, it probably is impossible to EVER finish running that many combinations.
Therefore, I am just wondering if I can do anything to make the code more efficiently? or it's just impossible to test 14 to the power of 25 combinations with a normal desktop computer???
To provide a better idea, I have uploaded the workbook macro I created
http://www.mediafire.com/?0d62kmerzp8c3g2
Below is the backgroud to the workbook uploaded:
To me, this is pretty much an IMPOSSIBLE-TO-SOLVE My professor gave
as a bonus question for the term.
The question is:
There are 25 Teams in Sheet "Table" in the workbook (Team A ~ Team Y)
Each team, there are 14 configurations
(eg. Team A-1, Team A-2...Team A-14)
Team B-1, Team B-2...Team B-14....and so on..)
In every game, all 25 teams picks a confiugation to play.
eg.
Game1: Team A-1, Team B-1, Team C-1... Team Y-1
Game2: Team A-1, Team B-1, Team C-1... Team Y-2
Game4499879580584837114515226624: Team A-14, Team B-14, Team C-14....Team Y14)
Out of all the games (combinations), he said there are few games with 5 or more players NOT playing at all.
Our job is to figure out which players they are.
Hence all the FOR/LOOP in the macro to test out all the combinations. Any more efficient way?
Thanks
I have written a macro
Code:
Sub test()
Sheets("Table").Select
Range("U1").FormulaR1C1 = "=NOW()"
Range("U1").Value = Range("U1").Value
Range("c1").Select
Application.ScreenUpdating = False
''''''''''' Copying different Team combinations to Sheet 2
For A1 = 1 To 14
Sheet2.Range("c1:c40").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A1).Select
For A2 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C41:C80").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A2).Select
For A3 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C81:C120").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A3).Select
For A4 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C121:C160").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A4).Select
For A5 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C161:C200").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A5).Select
For A6 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C201:C240").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A6).Select
For A7 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C241:C280").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A7).Select
For A8 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C281:C320").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A8).Select
For A9 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C321:C360").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A9).Select
For A10 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C361:C400").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A10).Select
For A11 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C401:C440").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A11).Select
For A12 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C441:C480").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A12).Select
For A13 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C481:C520").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A13).Select
For A14 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C521:C560").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A14).Select
For A15 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C561:C600").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A15).Select
For A16 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C601:C640").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A16).Select
For A17 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C641:C680").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A17).Select
For A18 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C681:C720").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A18).Select
For A19 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C721:C760").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A19).Select
For A20 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C761:C800").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A20).Select
For A21 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C801:C840").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A21).Select
For A22 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C841:C880").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A22).Select
For A23 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C881:C920").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A23).Select
For A24 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C921:C960").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
ActiveCell.Offset(40, -A24).Select
For A25 = 1 To 14
ActiveCell.Offset(0, 1).Select
Sheet2.Range("C961:C1000").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value
'Calculate
If Sheet2.Range("H1") > 5 Then 'If more than 5 Players are not playing, copy them to Sheet3
Sheets("Sheet2").Select
Range("F1").Select
Do While ActiveCell <> ""
If ActiveCell = 0 Then
Range(ActiveCell, ActiveCell.Offset(0, -1)).Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Sheets("Sheet3").Select
ActiveCell.Offset(2, 0).Select
Sheets("Table").Select
End If
Next A25
ActiveCell.Offset(-40, -14 + A24).Select
Next A24
ActiveCell.Offset(-40, -14 + A23).Select
Next A23
ActiveCell.Offset(-40, -14 + A22).Select
Next A22
ActiveCell.Offset(-40, -14 + A21).Select
Next A21
ActiveCell.Offset(-40, -14 + A20).Select
Next A20
ActiveCell.Offset(-40, -14 + A19).Select
Next A19
ActiveCell.Offset(-40, -14 + A18).Select
Next A18
ActiveCell.Offset(-40, -14 + A17).Select
Next A17
ActiveCell.Offset(-40, -14 + A16).Select
Next A16
ActiveCell.Offset(-40, -14 + A15).Select
Next A15
ActiveCell.Offset(-40, -14 + A14).Select
Next A14
ActiveCell.Offset(-40, -14 + A13).Select
Next A13
ActiveCell.Offset(-40, -14 + A12).Select
Next A12
ActiveCell.Offset(-40, -14 + A11).Select
Next A11
ActiveCell.Offset(-40, -14 + A10).Select
Next A10
ActiveCell.Offset(-40, -14 + A9).Select
Next A9
ActiveCell.Offset(-40, -14 + A8).Select
Next A8
ActiveCell.Offset(-40, -14 + A7).Select
Next A7
ActiveCell.Offset(-40, -14 + A6).Select
Next A6
ActiveCell.Offset(-40, -14 + A5).Select
Next A5
ActiveCell.Offset(-40, -14 + A4).Select
Next A4
ActiveCell.Offset(-40, -14 + A3).Select
Next A3
ActiveCell.Offset(-40, -14 + A2).Select
Next A2
ActiveCell.Offset(-40, -14 + A1).Select
ActiveCell.Offset(0, 1).Select
Next A1
Application.ScreenUpdating = True
Range("U2").FormulaR1C1 = "=NOW()"
Range("U2").Value = Range("U2").Value
End SubThe purpose is to caculate all 4499879580584837311451522624 combinations
(14 x 14 x 14 x 14...14 (14^25)
However, I realize with my computer's speed, it probably is impossible to EVER finish running that many combinations.
Therefore, I am just wondering if I can do anything to make the code more efficiently? or it's just impossible to test 14 to the power of 25 combinations with a normal desktop computer???
To provide a better idea, I have uploaded the workbook macro I created
http://www.mediafire.com/?0d62kmerzp8c3g2
Below is the backgroud to the workbook uploaded:
To me, this is pretty much an IMPOSSIBLE-TO-SOLVE My professor gave
as a bonus question for the term.
The question is:
There are 25 Teams in Sheet "Table" in the workbook (Team A ~ Team Y)
Each team, there are 14 configurations
(eg. Team A-1, Team A-2...Team A-14)
Team B-1, Team B-2...Team B-14....and so on..)
In every game, all 25 teams picks a confiugation to play.
eg.
Game1: Team A-1, Team B-1, Team C-1... Team Y-1
Game2: Team A-1, Team B-1, Team C-1... Team Y-2
Game4499879580584837114515226624: Team A-14, Team B-14, Team C-14....Team Y14)
Out of all the games (combinations), he said there are few games with 5 or more players NOT playing at all.
Our job is to figure out which players they are.
Hence all the FOR/LOOP in the macro to test out all the combinations. Any more efficient way?
Thanks
