Calculation on big files + progress

[djbubu99]

Hi,

I have a summary file wich basicly is a file that has links to 3 other files: A, B, C. I need to gather all my data in this summary and then used that data to create a pivot table.

In the summary file a have just a first row with table header and a second row with my formulas (most of them beeing vlookup formulas from files A,B,C).
I have a vba code that opens does files, finds the used range of file A and autofills the formula to the necessary row number, then updates the links and finally closes files A,B,C.

This can be a dailly or a monthly report based on the data that I use in A,B,C.
When I use that macro for a daily report there is no problem. It takes about 5 minutes, but it works.

I tryed using it for a monthly report, i waited about 4 hours till I decided to end that task. I don't know if this works.

My summary file has 25 columns and about 250.000 rows for the monthly report. The problem is that i don't know if it works (i just get a white screen in excel & i don't have a progress bar or something to show me what is the status on my workbook)

This is all done on an Intel Core 2 Duo, 3gb Ram, Windows Xp, Office 2007.


Is there an other aproach to this?

 

[VoG]

Try posting your code.

You can display progress using the status bar (or other methods) but I would guess that the user would be asleep after 4 hours

 

[Billy Hill]

If you're working down a spreadsheet, row by row, this will count the total rows and the current row so you'll know it's doing something...

I'm just copying this code from some of my own, so you'll have to check/set any variables/functions yourself and do some error trapping outside this little procedure in case of failure.

Application.ScreenUpdating = False ' Make it process faster
For i = 2 To lastRow ' Start on Row 2' Iterate through rows
    Set cellPointer = Worksheets(sWS).Cells(i, iRow) ' Get value of cell
    DoEvents
 
Your_Code_Here
 
'Show what's going on
sSuccess = FormatPercent(i / lastRow, 1)
Application.StatusBar = "Adding Links. Working on row " & i & " of " & lastRow & "  (" & sSuccess & ")"
   DoEvents
Next i ' Next row
Application.ScreenUpdating = True
Application.StatusBar = False

Hope this helps