SUMPRODUCT working in worksheet but not in VBA

[eloirjr]

I have this formula on sheet SUMPRODUCT(--(MONTH(Venda!$L$3:$L$5000)=MONTH($A9)); --(YEAR(Venda!$L$3:$L$5000)=YEAR($A9)); Venda!M3:M5000), it´s works fine but I have to translate to VBA code.
I have been tried
Sheets("Venda").Select
Range("A3").Select
Lastcl = Cells(Rows.Count, "A").End(xlUp).Row
TotParcPrev1 = Application.SumProduct(--Sheets("Venda").Range(Month("L3:L" & Lastcl)) = Month(tempdata), --Sheets("Venda").Range(Year("L3:L" & Lastcl)) = Year(tempdata), Sheets("Venda").Range("M3:M" & Lastcl)), but the total is empty. When I used evaluate, give the error 2015.
Maybe I´m making something wrong.
Thanks in advance

 

[DonkeyOte]

SUMPRODUCT in VBA is a little different - best to use an Evaluate approach:

Dim Lastcl As Long
Dim TotParcPrev1 As Double
With Sheets("Venda")
    Lastcl = .Cells(.Rows.Count,"A").End(xlUp).Row
    TotParcPrev1 = .Evaluate("SUMPRODUCT(--(TEXT(L3:L" & LastCl & ",""mmyyyy"")=TEXT(tempdata,""mmyyyy"")),M3:M" & LastCl & ")")
End With

 

[eloirjr]

Thanks for reply, but not work.
The TotParcPrev1 is 0, instead formula shows me 3343,00.

 

[DonkeyOte]

Little to add I'm afraid - the VBA replicates your existing approach
(uses tempdata per your VBA attempt rather than A9 as per your native formula - I assumed this was a named range [rather than a variable]).

If tempdata is blank and you have blank rows in L with values in M then you will generate a result - you may wish to adjust the formula logic accordingly
(ie pre-emptive IF or Number test against L)

 

[DonkeyOte]

OP has since hijacked thread: http://www.mrexcel.com/forum/showthread.php?t=490928 - have requested this thread be locked.

 

[eloirjr]

You´re right, I did the changes without sucess
With Sheets("Venda")
Lastcl = .Cells(.Rows.Count, "A").End(xlUp).Row
TotParcPrev1 = .Evaluate("SUMPRODUCT(--(TEXT(L3:L" & Lastcl & ",""mmyyyy"")=TEXT(A" & p + 8 & ",""mmyyyy""))*M3:M" & Lastcl & ")")
End With

Shows 0 yet
Thanks