possible to speed up calculation?

S

Snoopy.

New Member
Joined
Oct 15, 2011
Messages
6
Hi,

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 Sub




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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Probably not going to get many people to click through to the linked sheet to put it on their computers, we don't know where it's been ;) All the same, I do have some suggestions:

1. Reading the code, you are doing A LOT of cell Selecting and Offsetting. While it is good that you have turned ScreenUpdating off to speed things up, Selecting is an inefficient use of the macro's time. Instead, you should refer to the cells you want using the variables you are already incrementing.
2. Once you get to the middle of the For / Next loops, you do a Select - Copy - Select - Paste Values. Instead, you could do a cells(x,y).value=cells(p,q).value
3. Sheet2 has at least one formula working (cell H1), and maybe more than that. If you can bring the math into the macro, it will be less burden on the processor, running only when you want instead of (possibly) every time you select a cell or change a cell's value anywhere in the sheet. When I'm doing a macro with lots of loops, I try to have no spreadsheet formulas, none. The sheet is for data display only.
4. Continuing the thought above: if you must have spreadsheet formulas (teacher insists?), you might want to set Application.Calculation = xlManual before starting the massive loops, then doing a Calculate only when YOU want.
5. You might consider bringing the team combination data into an array variable inside the macro, instead of referring to spreadsheet cells.
6. You should Dim all your variables. This may not speed things up, but it is a good practice.
7. Brute-Force might not be the best approach. Even within a brute-force, you might be able to short circuit some of it. I'm guessing that each of a given team's 14 configurations has a different number of active players this game, right? If the configuration you are looking at uses every player, it won't contribute to a 5-inactive and could be skipped. You only care about sets with inactive players. In fact, if a particular team has no configurations with inactive players, you can ignore that team altogether.


Good Luck, keep us posted, and I hope you get your bonus credit!
 
Upvote 0
Hi Garndertoo,

Thanks a lot for trying to help. :)
I was worried that no one would try to help as this question seemed quite impossible to solve.

- So, just so I can get more input from people and get more suggestions, where can I upload the file/what should be the proper way??




Furthermore, before getting a reply from you, I tried to just run a blank For/Next loop 1 to 14 for 25 times WITHOUT any data or any other instructions.

Code: 
Sub test()

For A1 = 1 To 1
 
    For A2 = 1 To 14
    
        For A3 = 1 To 14
 
            For A4 = 1 To 14
         
                For A5 = 1 To 14
                                      
                    For A6 = 1 To 14
                 
                        For A7 = 1 To 14
                  
                            For A8 = 1 To 14
                                 
                                 For A9 = 1 To 14
                              
                                    For A10 = 1 To 14
                                               
                                        For A11 = 1 To 14
                                   
                                            For A12 = 1 To 14
                                     
                                                For A13 = 1 To 14
                                     
                                                    For A14 = 1 To 14
                                       
                                                        For A15 = 1 To 14
                                                         
                                                            For A16 = 1 To 14
                                      
                                                                For A17 = 1 To 14
                                            
                                                                    For A18 = 1 To 14
                                                         
                                                                        For A19 = 1 To 14
                                                                        
                                                                            For A20 = 1 To 14
                                                                          
                                                                                For A21 = 1 To 14
                                                                          
                                                                                    For A22 = 1 To 14
                                                                            
                                                                                        For A23 = 1 To 14
                                                                             
                                                                                            For A24 = 1 To 14
                                                                              
                              
                              
                              
                
                
                
                
                                                                                                For A25 = 1 To 14
                                                                                 
   
                                                                                                Next A25
                                                                                                    
                                                                                            
                                                                                            
                                                                                            
                                                                                            
                                                                                           Next A24
                                                                                                    
                                                                                  
                                                                                  
                                                                                        Next A23
                                                                                                                             
                                                                                                                             
                                                                                    Next A22
                                                                                                    
                                                                                                    
                                                                                Next A21
                                                                                                    
                                                                                                    
                                                                            Next A20
                    
                    
                                                                        Next A19
                                                                                                       
                                                                                                       
                                                                    Next A18
                    
                    
                                                                Next A17
                    
                    
                                                            Next A16
                    
                    
                                                        Next A15
                    
                    
                                                    Next A14
                    
                    
                                                Next A13
                 
                 
                                            Next A12
                    
                    
                                        Next A11
                    
                    
                                    Next A10
                        
                        
                                Next A9
                                        
                                        
                            Next A8
                    
                    
                        Next A7
                    
                    
                    Next A6
                     
                     
                Next A5
                
                
            Next A4
        
        
        Next A3
    
    
    Next A2
    
    
    
Next A1
 
End Sub


As expected, this is A LOT faster and it SEEMS like it is possible to finish running all the loops within a reasonable time.

Therefore, it makes me feel MAYBE it IS possible to test all the combinations given the design/coding is more efficient?

Thanks
 
Upvote 0
Hi,

I have uploaded the file to Google Document. So it should be safer because it can be previewed/virus scanned.

However, it cannot uploaded with the macro code as it gets to transfer to the Google Document format..

So the macro code below has to be copy/pasted after the file is downloaded.

Thanks.

https://docs.google.com/spreadsheet...HlpSldjeFVpcWE2RTFyX291bjhpa2c&hl=en_GB#gid=2




Code: 
Sub test()
Sheets("Sheet1").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("Sheet1").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 Sub
 
Upvote 0
- Why do we see, for instance, Team A-1 appear 4 times?
- What does Sheetxxx mean in column A?
- If a name appears twice in a team, not only in Team A but also e.g. Team A-8, what does this mean? Is it twice the same player?
 
Upvote 0
Hi Wigi,

Thanks for the reply. i got some clarification from the professor.

- Team A-1..etc appearing 4 times is just for labeling purpose.
- Sheet 101/102/103/104 are also just for labeling purpose
((I guess he probably originally 'calculated' however way he did, and he just pasted the result in Sheet 1 as the bonus question (without deleting the uncessary labels! i confirmed with him, he said it's fine to ignore/delte those labels, so I have removed them from the excel file)

- Yes, if a name appears twice in each configuration, it is just seen as the same player. Again, I confirmed with him. So i suppose it's just "sloppy data"? But he said that would NOT affect the result, as the purpose is to find out WHICH configuations/combination would generate a generate the result that 5 OR MORE players ARE NOT ACTIVE. Actually, we're not required to provide which configuration/combination. Rather, just to name which 5+ players.

Thanks a lot for the help, especially on such impossible question.
I wonder, if by just doing every single combination, would the processing speed be faster in other programming environment..? or it would be somewhat the same regardless?

yikes...about the bonus qeustion:eeek:

but thanks a lot for trying to help :)
 
Upvote 0
If you look at column C, rows 2 to 33, why do we see 4 blocks of names? Is this 1 team (A-1), or what?
 
Upvote 0
Hi Wigi,

Yes, that is 1 out of 14 configurations from Team A. (Team A-1).

I am sorry for havent be able to make it clear.

Basically, each configuration is 1 column, 40 rows. Hence, when you see the macro, you will see my approach right now is to just copy EVERY SINGLE configuration (which is every 1column, 40 rows) to Sheet2
<<eg.
Team A-1: ((macro for a1

Sheet2.Range("c1:c40").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value



Team B-1: for a2 = 1 to 14
Sheet2.Range("C41:C80").Value = Sheet1.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(39, 0)).Value

and so on..
>>

, and then do a countif to see which players show a "0". If there are more than 5+ players with "0" for that certain combination, I copy the names to Sheet3.

However, this approach/coding seems to take forever which becomes impossible to calculate.

Thanks again, Wigi.
 
Upvote 0
If a name like Dylan appears in Teams A and B, is it the same player?
 
Upvote 0
Yes, I would assume it's the same player. (i guess i never thought much ^^")

If it is copied to Sheet2. Countif(c:c:, "dylan") would return <> 0.

So in that combination, dylan definitely would not be 1 of the 5+ inactive members as he shows up 2+ times.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnZ1156
  • Question Question
Check if date exists in entire Date column of Table4
Replies
1
Views
286
Tetra201
Tetra201
N
  • Question Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
480
DanteAmor
DanteAmor
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
555
MagicCarpet
M
M
  • Question Question
Not running code
Replies
0
Views
221
MagicCarpet
M
A
  • Question Question
Is there a way to speed this up?
Replies
4
Views
333
jolivanes
jolivanes

Forum statistics

Threads
1,225,071
Messages
6,182,685
Members
453,132
Latest member
nsnodgrass73

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top