Solved

Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07
16
700 Views
Last Modified: 2012-08-14
I am lookinfg at implementing obtaining a Sumproduct from read-only Excel sheets. For practice, I used a sheet with 1300 rows and placed the formula

=SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

which correctly obtained 19141 (Units sold).  I wrote a quick OLEDB Jet4.0 query on the Excel sheet as
SELECT Sum(UNITS) AS n FROM [Data$] WHERE (RANGE="BOOKS" AND REGION=6101);

which also obtained 19141 units. But wishing to stay with Excel I tried VBA

n = application.SUMPRODUCT((RANGE="BOOKS"),(REGION=6101),(UNITS))
If VarType(n) = 10 Then
   sProd = "Value Returned Error"
else
   sProd = n
end if

which returns error.  What am I doing wrong?

stan

P.S. I posted this question earlier, but it doesn't show up, so I am repeating the post. If it duplicates, I apologize.
0
Comment
Question by:stanl
[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
  • 6
  • 4
16 Comments
 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10793446
Try:

[replace 'Book.xls' with you workbook-name ]:

N = Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )

if N=10 then
        sProd = "Value Returned Error"
else
        sProd = n
end if

==============================
OR
==============================

N = Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )

if N=10 then
        sProd = msgbox("Value Returned Error")
else
        sProd = msgbox(n)
end if

==============================
OR
==============================

if Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )=10 then

        sProd = msgbox("Value Returned Error")

else

        sProd = msgbox(n)

end if

==============================
OR
==============================

if Application.WorksheetFunction.SumProduct(Range("Book.xls!BOOKS"), Range("Book.xls!REGION"),Range("Book.xls!UNITS") )=10 then

        sProd = "Value Returned Error"

else

        sProd = n

end if
0
 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10793471
What does "Region=6101" mean? Is there cell range called "Region", or "Region=6101", or "6101"?
0
 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10793597
As you can see, the function you would be using is a "WorksheetFunction" i.e. SumProduct, written like this:

Application.WorksheetFunction.SumProduct(  ..., ..., ..., ...)

You have to put between those commas, either names of named ranges or juts plain ranges on the current worksheet. To get the value you assign the result to a variable (as you've done already.)

Then if you want a message box to display the results, you use the msgbox("...") function. Or you might want to assign the value to a variable.

To refer to a named range like 'BOOKS' for example, you do this:

                                   Range("Book.xls!BOOKS")

where "Book.xls" is the name of the workbook.

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 1

Expert Comment

by:doubleglazing2
ID: 10793641
OK. I just realised that you're checking for errors using VarType. It's prbably best to ignore that (i.e. cut it out) and then see whats going wrong with your code. Even if you DO put it back in the code, its probably best to say "if VarType(n) <> 2 then ..." because as stands now, you can get a date or currency value from n, and "VarType(n)" will return you false i.e. no errors. Best to check it's an integer or combine an integer-check with a check for nullity or emptiness i.e.

"if (VarType(n) <> 2) AND (Vartype<>0) then ..."

or what about:

"if VarType(n) <> 2 AND (VarType(n) =  (0 or 1)) then ..."
0
 

Author Comment

by:stanl
ID: 10793960
You are confusing me.  I am just trying to take something that I know already works, either as function embedded in an Excel cell, or as the results of an OLEDB query, and obtain the same results with VBA.  Therefore I already know I am not going to get a date or currency value, hence I am only looking for an error.  Yes, both RANGE and REGION are ranges as well as column headings

RANGE   ....    REGION      UNITS
BOOKS           6101            100
BOOKS           6102              59
PETS              6101            110
CARS             6101             200
BOOKS            6101               47


Do I have to use either Eval() or Union() to get the conditional ranges?
0
 
LVL 1

Expert Comment

by:VosJ
ID: 10821591
I think you have to replace the , with *

n = application.SUMPRODUCT((RANGE="BOOKS"),(REGION=6101),(UNITS))

like

n = application.SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

0
 

Author Comment

by:stanl
ID: 10822322
That Produces an "Unknown Runtime Error"
0
 
LVL 1

Expert Comment

by:VosJ
ID: 10830921
I've tested it and this code gives me a good result.

Don't forget to adapt the code to the correct names and captions.

Dim myFormula As String

myFormula = "SumProduct((rangex=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"
           
n = Evaluate(myFormula)

Debug.Print n
0
 

Author Comment

by:stanl
ID: 10831993
That gives a "type mismatch" error (I changed rangex to RANGE as that is the name used in the worksheet)
0
 
LVL 1

Expert Comment

by:VosJ
ID: 10832886
You have to be carefull with the name 'range' because that is an already excisting word in Excel VBA.

You better should rename RANGE to something else.
0
 

Author Comment

by:stanl
ID: 10833350
yes, I changed it to

myFormula = "SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"

and received "type mismatch"

I then passed the application object to my function and tried

myFormula = "application.SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"

which evaluated to a vartype of 10, or an error...

0
 
LVL 1

Expert Comment

by:VosJ
ID: 10833548
I tested my code under Excel97 en Excel2000, and both gave the same result.

I used your small table as test.
rng        region    units
books      6101      100
books      6102      110
pets      6101      59
cars      6101      120
books      6101      47
*******
This is the complete code for the macro.
Sub macro1()

Dim myFormula As String

myFormula = "SumProduct((rng=" & Chr(34) & "books" & Chr(34) & ") * (region=6101) * (units))"
           
n = Evaluate(myFormula)


Debug.Print myFormula

Debug.Print n

End Sub

*******
And this is the result:

SumProduct((rng="books") * (region=6101) * (units))
 147

The only thing for the moment that I can think off is related to SumProduct itself. The columns used must have the same number of elements.
0
 

Author Comment

by:stanl
ID: 10834307
I pasted your code into a new macro, made the necessary changes and ran it - nothing happened.

If you would like, I can send you the worksheet via email?
0
 
LVL 1

Expert Comment

by:VosJ
ID: 10835154
Yes, feel free to send me the worksheet. The email-adres is jan@orlahdata.be.
0
 
LVL 1

Accepted Solution

by:
VosJ earned 250 total points
ID: 10867590
I replied to your mail a few days ago. Apparently it did not arrive. I'm resending the email. If you don't receive the mail in the next hours, please let me know. Maybe there is somewhere in the line a firewall blocking xls-files.
0
 

Author Comment

by:stanl
ID: 10871655
I received your revisions/macro; I guess my problem is I was trying to do it from within a .wsc file using VbScript, which uses eval() not evaluate(). But thanks again.
0

Featured Post

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
sameEnds challenge 25 124
object oriented javascript web form 8 191
mapBully challenge 6 195
Re-position sub-options beneath the TAB 7 111
This article will show, step by step, how to integrate R code into a R Sweave document
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

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