Faster Code

[Braddrum]

Can anyone help me speed up this code? It takes way too long to run

Dim file As Integer, x As Integer, rw As Integer, cl As Integer
    Dim fl As String
    Dim count As Integer
    
    Cells(1, 37).Select

    fl = Dir("C:\(Path)" & "*.xls")
    Do While Len(fl) > 0
        ActiveCell.Formula = fl
        ActiveCell.Offset(1, 0).Select
        fl = Dir()
    Loop
    
    Cells(8, 37) = ""
    x = Cells(Rows.count, 37).End(xlUp).Row
    
    For cl = 1 To 7
    For rw = 2 To 8761    
    For file = 1 To x
        Cells(3, 36) = "='C:\(path)[" & Cells(file, 37) & "]Sheet1'!A" & rw & ""
        Cells(2, 36) = Cells(2, 36) + Cells(3, 36)
    Next file
    
    Cells(rw, cl) = Cells(2, 36) / 7
    Range("AJ2").Clear
        
    Next rw
    
    Next cl

This program averages 7 worksheets from different workbooks cell by cell for column A2:A8761 (hours in a year), but essentially never finishes, I know I could use more ram, but I am afraid it wouldn't help that much, I currently have 2GB, the program usually stops around row 5000, but that is after about 10 minutes. Is there a way to speed this up?

Thanks!

 

[pbornemeier]

Have you tried to copy Sheet1, Range A2:A8761 from each of the workbooks to a Summary workbook then perform the averaging there using in-cell formulas?
Workbook 1 to Summary Workbook, Column A
Workbook 2 to Summary Workbook, Column B
...
Workbook 7 to Summary Workbook, Column G
then formulas in column H2:

=IF(ISERROR(AVERAGE(A2:G2)),"",AVERAGE(A2:G2))

Copied down to H8761

 

[Braddrum]

Thanks for the reply

If at all possible I would like to automate the process, in the end i will have to do this average 125 times for all of my various workbooks, and will have to expand this from A2:AH8761, I am just trying to piece this problem together bits at a time

Brad.

 

[pbornemeier]

I would then copy data from each worksheet, cells A2:AH8761 into a summary workbook (using copy/paste special - values), then use a summary worksheet in the summary workbook to perform the averaging. Turn on the macro recorder and do this with a subset of the data (A2:H20 perhaps) from each worksheet to get a feel for how the code works then expand it to cover the entire range.

Ensure all of the copied data worksheet tabs were between the S and E worksheets then use a formula like this =AVERAGE('(S)E)'!A2) with the S and E worksheets blank, then the result would be the average of all cell A2 of all the worksheets between the S and E worksheets.

S and E stand for Start and End - can be any valid names.

The other option would be to have the worksheets in the summary workbook permanently linked to the appropriate cells in the input worksheets. When the summary workbook was opened it would pull data from the input worksheets. I am not sure which of those methods would update quicker, but either would be quicker than adding each cell individually for each worksheet.

 

[Braddrum]

Thanks Phil, I will try that out and let you know how it goes.

Brad.

 

[Braddrum]

Thanks again for the help, it did the trick and it is automated/much faster than what I was doing before.

Brad