Solved
Stuck with VBA and SumProduct in Excel
Posted on 2004-04-07
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.