Large sumproduct issues in vba (error statements and improvements?)

[Gunblade]

Hi all,

Have this rather cumbersome evaluate with sumproduct that I have in vba that has two points I'd like to ask about.

Note that AdvancedEvaluate is just some function code I found from stackoverflow which bypasses the 255 limit by copying and pasting the formula into another workbook before getting the result from there (as I said overly cumbersome).

Please Login or Register  to view this content.

Now as it stands I don't think I need to explain the code, I'm just showing it for reference because the questions are:

1. How to encapsulate that in an iferror statement. I've seen one solution where it's iferror into every individual range which seems overly redundant and for this code, will make it ever slower than it already is given the huge number of conditions required (And they're all absolutely required), the number of iterations this will go through and the rows of data it has to search through (nearly 400,000 rows).
Some later iterations will have a divide by 0 error as only some products in the range will have to be 0.

2. Is there actually anyway in VBA to make something like a sumproduct more efficient rather than using evaluate then a ridiculously long formula over a ridiculous number of rows or is this the best it can get? Not expecting someone to rewrite my formula as of course I've not explained what I'm doing but was wondering if maybe there was a faster "substitute" for example. Everything I've read so far just says "use evaluate and sumproduct". This clearly is not an efficient idea in my case.

If you think you need an (lengthy) explanation let me know, thanks :P

[mjr veverka]

Attach a short example showing the data layout - 3 to 4 rows of data - with false data (to protect confidentiality, etc.).

[Gunblade]

Attached example with 50 data rows.
Data set obviously with reduced complexity against real set.
No need to offer a complete solution for the efficiency part of the question, unless your into that sort of thing. (I won't complain )
Hints of where to look and how to accomplish it would be appreciated however if you think there's a way to improve it.

Though will probably need a solid answer on the error issues, at the moment I'm using the dirty method of find/replacing the div/0 error with 0's in code after the loop is completed (maybe that's good enough?)