Sumproduct problem from the VBA

[jgyenese]

Hi All,
I am going to use sumproduct function from VBA. My problem is that the first msgbox is simple doesn't work. (I attached a small xl file with the VBA code)
Maybe somebody has an idea to correct this code.
Thx and rgds,
Janos
-----------------------

[VBA]
Sub Test2sumproduct()
Dim r1 As Range
Dim x As Variant
Dim i As Integer
Dim StartDate As Date
Dim Cat As Range
Dim tCatName() As String
Dim iCatNumber As Integer
Sheets("sheet1").Select
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select

Set r1 = Selection
x = r1.Value
ReDim tCatName(UBound(x, 1))
iCatNumber = UBound(x, 1)
For i = 1 To iCatNumber
tCatName(i) = Cells(i + 1, 12)
Next i
StartDate = Cells(1, 10) - 1
MsgBox ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=""& StartDate & "")*--(B3:B300=""& tCatName & "")*--(C3:C300))")
MsgBox ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=J1)*--(B3:B300=""a"")*--(C3:C300))")
MsgBox ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=J1)*--(B3:B300=""a"")*--(d3:d300)*--(E3:E300=""X""))")
End Sub
[/VBA]

[Andy Pope]

The problem is that tCatName is an array variable so requires you to identify which element of the array you want to use.

MsgBox ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=" & _
    StartDate & ")*--(B3:B300=""" & _
    tCatName(1) & """)*--(C3:C300))")

[jgyenese]

The problem maybe deeper, because I got a "Run-time error '13': Type mismatch.


For j = 1 To iCatNumber
MsgBox ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=" & _
StartDate & ")*--(B3:B300=""" & _
tCatName(j) & """)*--(C3:C300))")
Next j

Any idea ....

thx: Janos

[Andy Pope]

I had no such problem using your latest code in your example.

[jgyenese]

It is very strange. I just check it one other machine (the Office 2003 Excel ver. 11.8033.8036, SP2), and the code work fine. The earliest the version was the following (Office 2003 Excel ver. 11.5621.5606). This case the code said the "run-time error".

thx
J?nos

[jgyenese]

Andy,
The code is running, but somehow could not take into account the date criteria. It could be see in the attached Excel file.
(I removed all non A category and left only 1 records before 1st of Sept. 2006, the result by code is: 490896 and the correct sum is 435984)

For j = 1 To iCatNumber
Cells(j + 1, 13) = ActiveSheet.Evaluate("SUMPRODUCT((A3:A300>=" & _
StartDate & ")*--(B3:B300=""" & _
tCatName(j) & """)*--(C3:C300))")
Next j


Any further idea???

Janos

[Bob Phillips]

I get 435984, but you may want to amend the code to

[vba]

Cells(j + 1, 13) = ActiveSheet.Evaluate("SUMPRODUCT((A3:A300>=""" & _
Format(StartDate, "yyyy-mm-dd") & """),--(B3:B300=""" & _
tCatName(j) & """),C3:C300)")
[/vba]

[Charlize]

I can't see any problem. When I change your startdate (on the sheet) to 31/08/2006 it displays the correct result (490.896 on sheet and 490896 on msgbox). When changing to 05/09/2006 (381.072 on sheet and 381072 on msgbox).

Charlize

[jgyenese]

The problem still exist. The calculation by the code and the formula on the sheet still non equal. It can be seen that the formula shows the correct sum. As I see, the code do not calculate using the StartDate.

CategorySum (by code)Sum (by formula)A 490 896 435 984 B 195 720 130 440


I attached the xl file (now I remove the message box and put to the sheet the formula).

The table is very simple
dateCategory1Number1Number2Comment2006.08.30 B 65 280 02006.08.31 A 54 912 4942082006.09.01 A 54 912 4942082006.09.05 A 130 440 -53160 X2006.09.06 A 65 280 02006.09.09 A 54 912 4942082006.09.13 A 130 440 -53160 X 2006.09.14 B 130 440 -53160 X

Any idea......


Janos

[Charlize]

If your startdate is cell J1 why not use J1 instead of a variable. It seems that this works. Using excel 2003 the reference to the cell J1 works but with a variable it simply counts all the numbers.
[vba]Cells(j + 1, 13) = ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=J1)* & _
--(B3:B300=""" & tCatName(j) & """)*--(C3:C300))")[/vba]
Charlize

[jgyenese]

Charlize, This is a very good idea, thank you. I just try and works well.

Janos

[Bob Phillips]

If your startdate is cell J1 why not use J1 instead of a variable. It seems that this works. Using excel 2003 the reference to the cell J1 works but with a variable it simply counts all the numbers.
[vba]Cells(j + 1, 13) = ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=J1)* & _
--(B3:B300=""" & tCatName(j) & """)*--(C3:C300))")[/vba] Charlize

That won't work, you have wqrongly continued the line.

And a * operator is NEVER needed as well as the double unary --.

[vba]

Cells(j + 1, 13) = ActiveSheet.Evaluate("SUMPRODUCT(--(A3:A300>=J1)," & _
"--(B3:B300=""" & tcatname(j) & """),--(C3:C300))")

[/vba]