Solved

Using Sumproduct in VBA

Posted on 2010-08-31
6
1,299 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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