VBA to replace in sheet SUMPRODUCT function?

[TonyThomas]

Hello

I am using Excel 2007. I have a worksheet with 200,000 rows of data in it- it was not my choice to set it up this way, I inherited the project!

The last column in the range of 200,000 rows is a calculated field that uses SUMPRODUCT to match certain column values for the current row against values in the entire data set.

Here is an example of the function. It is copied down the entire column for all 200k rows:

=IF(
ISERROR(V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0))),0,V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0)))

The IF uses ISERROR function to evaluate the expression using values relative to the current row. If the expression results in an error zero is displayed, else the expression is used. The expression divides the value in column V for current row by the total number of rows that that match the current row based on the values in three coulmns (J,E, and V). The function must loop over the entire range of rows for EACH row and as you can imagine this really sucks. It takes >10 hours to recalculate the workbook.

Can anyone recommend a better solution to achieve what I've done with the above formula using VBA or anything else? I would really appreciate the assistance!

Thanks!

-TT

[rylo]

Hi

Try something like this.

Sub aaa()
  For i = 7 To 200000
    Cells(i, "F").Value = Evaluate("=IF(ISERROR(V" & i & "/SUMPRODUCT(($J$7:$J$200000=J" & i & ")*($E$7:$E$200000=E" & i & ")*($V$7:$V$200000>0))),0,V" & i & "/SUMPRODUCT(($J$7:$J$200000=J" & i & ")*($E$7:$E$200000=E" & i & ")*($V$7:$V$200000>0)))")
  Next i
End Sub

You will have to update the part

Cells(i, "F")

to nominate which column contains your formula.

One you are comfortable that it is working properly, you could turn off the screen updating, perhaps add a statusbar message so you know your status, and if there is anything that depends on this formula then turn off the calculation until it is all done, then turn it on again.

rylo