Solved

Using Sumproduct in VBA

Posted on 2010-08-31
6
1,330 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:Tony_Rhoades
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 33569337
The SUMPRODUCT function in a worksheet formula is a very powerfull tool for applying array formula conditional logic to problems. From VBA however, the SumProduct function (available via the Application or Application.WorksheetFunctions objects) is not as flexible and complex array constucts cannot be used. The reason is the parameter typing is much more strict from VBA and each parameter passed to SumProduct must be an array - it cannot be a string. While the following example formula:

   =SUMPRODUCT(A1:A10,B1:B10)

can be translated into a VBA form:

   MsgBox Application.SumProduct([A1:A10], [B1:B10])

This more complex version:

   =SUMPRODUCT((A1:A10=1)*B1:B10)

can not be translated.

To use the more complex forms of SUMPRODUCT syntax, use the Application.Evaluate function:

   MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=1)*B1:B10)")

Kevin
0
 

Author Comment

by:Tony_Rhoades
ID: 33569476
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?
0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 250 total points
ID: 33569478
For the number of non-blank cell you can also try..
Sub CntNonBlanks()
    Set RngCells = Range("A7:A1000")
    For Each rngcell In RngCells
        If rngcell <> "" Then i = i + 1
    Next rngcell
    MsgBox i & " Non-blank Cells in the Range"
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33569500
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 33569570
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
0
 

Author Closing Comment

by:Tony_Rhoades
ID: 33569803
Thank you both for your help.  Either of these solutions will do the trick
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question