Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Sumproduct in VBA

Posted on 2010-08-31
6
Medium Priority
?
1,414 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
6 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

885 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