Solved

Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07
16
696 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PYTHON: Updating local variable in .TPL file 24 137
trying to start a c# program 5 88
notReplace  challenge 53 115
Modbus - whats the maximum I can store in one register? 4 78
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now