asked on

Using Sumproduct in VBA

I am trying to convert a working Excel Funtion "=SUMPRODUCT((A7:A1000<>"")*(A7:A1000<>0))" to VBA Code as follows:

Set y = WorksheetFunction.SumProduct(("A7:A1000" <> "") * ("A7:A1000" <> 0))
Range("A2").Value = y

The problem is I am getting a "Compile Error: Type Mismatch."

I am not familary enough with the SumProduct to understand what is causing the error. VBA will not allow me to use "On error resume next."

I am attempting to count non-blank cells in range of cells containing cells which contain formulas.

Please advise.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tony_Rhoades

ASKER

I understand your explanation of why I cannot use the WorksheetFunction.

If I am following you correctly I should be able to use the following code in VBA:

Range("A2").Value = Application.Evaluate("SumProduct((A7:A1000 <> "") * (A7:A1000 <> 0))")

however it returns #value!

I must be missing something?

SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zorvek (Kevin Jones)

The SUMPRODUCT will operate on the active sheet. If the desired target sheet is not active then you need to use external references:

Range("A2").Value = Application.Evaluate("SumProduct((Sheet1!A7:A1000 <> """") * (Sheet1!A7:A1000 <> 0))")

Also, be careful with your double quotes. You have to escape them to use them in a string.

Kevin

byundt

I use the information in the following PAQ: http:/Q_20896095.html If you read through to the end, several alternative means of using SUMPRODUCT in VBA are shown.

In general:
1) You must use the comma form of SUMPRODUCT rather than multiplying one Boolean expression by another
2) You must coerce the Boolean expression into 1 and 0. SUMPRODUCT won't do it for you.

Brad

Tony_Rhoades

ASKER

Thank you both for your help. Either of these solutions will do the trick