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

x
?
Solved

Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07
16
Medium Priority
?
719 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
  • 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
Technology Partners: 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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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.
Suggested Courses

916 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