Formula / Memory Limitations?

[debauch]

Hello,

I previously had a post regarding dynamic Sumproduct, which I've managed to workout successfully through code. It puts in the formula, and pastes it as values.

HOWEVER, the data is 32,000 rows of data, and when the code tries to put the formula down 32k rows, it times out and I need to crash it. I waited over 60 min for it to finish, and it couldn't.

I've turned off screen updating and automatic calculations off while it attempts to run the code but still fails?? Is there any code that might help allow this to run, or is the formula just a pure memoy hog, and won't work???

'.Range("L1") = "Time_Daily"
Set rColL = .Range(.Cells(2, "L"), .Cells(glForeMaxRow, "L"))
rColL.FormulaR1C1 = "=SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS)*(Interval_Time)) _
/SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS))"
rColL = rColL.Value 'turns to value

[mikerickson]

I'd try this. The screen updating and calculation off will speed things up. The two testing lines can be removed once you know what is taking all that time.

Application.ScreenUpdating = False
Application.Calculation = xlManual

'.Range("L1") = "Time_Daily"
Set rColL = .Range(.Cells(2, "L"), .Cells(glForeMaxRow, "L"))
rColL.FormulaR1C1 = "=SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS)*(Interval_Time)) _
/SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS))"

Msgbox "Formulas loaded, ready to calculate"
Calculate
Msgbox "Calculation done, ready to finish"

rColL = rColL.Value 'turns to value
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

[Paul_Hossler]

I've always used SUMPRODUCT, etc. as an array formula to summarize data, so I'm having a hard time seeing the reason to putting it on every row like that. Can you post a _small_ WB as an example with just the data and the desired outcome?

Paul

[debauch]

Mikerickson, It never made it to the first msgbox.

Paul, I have it run for each row for the reason that a vlookup (against row A) is being used on another sheet to pull back the daily total. Since lookup's only pull the first results it finds , I just made it go in every line.

I posted a sample of the data and the formula's being used. I've had it work w/ 3000 rows, but 30k is white screen all day.

[mikerickson]

Are any of those named ranges dynamic?
I just tested by filling A1:A32,000 with that formula.
For i = 1 To 100
Range("a1:a32000").FormulaR1C1 = "=SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS)*(Interval_Time))/SUMPRODUCT((Fore_Switch_Int_Program=RC2)*(CALLS))"
Next itook 10.16 sec., so something else must be slowing things up.