SUMPRODUCT working in worksheet but not in VBA - please help!

[nick_clarke]

I've developed a sumproduct function that works perfectly when ran within my worksheet, however I am receiving a type 13 mismatch error when attempting to use this via VBA. My code is as follows:

dim t as integer

t = Evaluate("=SUMPRODUCT(('Sheet 1'!$W$2:$W$15994>=B19)*(('Sheet 1!$W$2:$W$15994<=B20))*('Sheet 1'!$V$2:$V$15994="M - filed as complete")")

txttest1.Value = t

I'm sure it is something to do with the "M - filed as complete" within the final criteria section, but I am not sure what the problem is.

I've been searching the internet for hours trying to find a solution and I would be really grateful if someone could please help me.

Thanks

 

[nick_clarke]

Thanks for the quick reply.

I appear to have solved the problem - it was the data within the F column.

One of the cells apeared to have a date held within it, however excel wasn't treating it correctly under the function. I simply deleted it and then rekeyed the data and then the vba worked.

Thanks for all your assistance, it is greatly appreciated.

 

[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

 

[Andrew Poulsom]

This worked for me:

TotParcPrev1 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!L3:L" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!L3:L" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!M3:M" & Lastcl & ")")

 

[eloirjr]

Thanks, now it´s working, but the total amount is less than the current numbers on the range. I did it
TotParcPrev1 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!L3:L" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!L3:L" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!M3:M" & Lastcl & ")")
TotParcPrev2 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!o3" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!o3" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!p3" & Lastcl & ")")
TotParcPrev3 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!r3:r" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!r3:r" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!s3:s" & Lastcl & ")")
TotParcPrev4 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!u3:u" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!u3:u" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!v3:v" & Lastcl & ")")
TotParcPrev5 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!x3:x" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!x3:x" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!y3:y" & Lastcl & ")")
TotParcPrev6 = Evaluate("=SUMPRODUCT(--(MONTH(Venda!aa3:aa" & Lastcl & ")=MONTH(" & tempdata & ")),--(YEAR(Venda!aa3:aa" & Lastcl & ")=YEAR(" & tempdata & ")),Venda!ab3:ab" & Lastcl & ")")
totPrevistoFinal = TotParcPrev1 + TotParcPrev2 + TotParcPrev3 + TotParcPrev4 + TotParcPrev5 + TotParcPrev6
Sheets("Fluxo").Cells(p + 8, 2).Value = totPrevistoFinal
It shows me 87,80 for Aug-2010
I has using Sheets("Fluxo").Cells(p + 8, 2).FormulaR1C1 = "=SUMPRODUCT((TEXT(Venda!R3C12:R5000C12,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C13:R5000C13)+SUMPRODUCT((TEXT(Venda!R3C15:R5000C15,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C16:R5000C16)+" & Chr(10) & "SUMPRODUCT((TEXT(Venda!R3C18:R5000C18,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C19:R5000C19)+" & Chr(10) & "SUMPRODUCT((TEXT(Venda!R3C21:R5000C21,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C22:R5000C22)+" & Chr(10) & "SUMPRODUCT((TEXT(Venda!R3C24:R5000C24,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C25:R5000C25)+" & Chr(10) & "SUMPRODUCT((TEXT(Venda!R3C27:R5000C27,""mmmaaaa"")=TEXT(RC[-1],""mmmaaaa""))*Venda!R3C28:R5000C28)", the total amount is 14036,25, this is a correct total.
there is something wrong.

 

[Andrew Poulsom]

It's impossible to diagnose your problrm without seeing some sample data.

 

[eloirjr]

There is some way to attach the file or send?

 

[eloirjr]

If there is not change to attach file, so, how can I use this form in VBA code
=SUMPRODUCT((TEXT(Venda!$L$3:$L$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$N$3:$N$5000)+
SUMPRODUCT((TEXT(Venda!$O$3:$O$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$Q$3:$Q$5000)+
SUMPRODUCT((TEXT(Venda!$R$3:$R$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$T$3:$T$5000)+
SUMPRODUCT((TEXT(Venda!$U$3:$U$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$W$3:$W$5000)+
SUMPRODUCT((TEXT(Venda!$X$3:$X$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$Z$3:$Z$5000)+
SUMPRODUCT((TEXT(Venda!$AA$3:$AA$5000;"mmmaaaa")=TEXT(A9;"mmmaaaa"))*Venda!$AC$3:$AC$5000)
I have 6 column with date and 6 column with value, I need sum based month and year
Thanks

 

[Andrew Poulsom]

I suspect that there is a limit to the number of characters that can be passed to the Evaluate method. This worked:

MsgBox Evaluate("=SUMPRODUCT((TEXT(Venda!$L$3:$L$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$N$3:$N$5000)" & _
                "+SUMPRODUCT((TEXT(Venda!$O$3:$O$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$Q$3:$Q$5000)")

but this doesn't:

MsgBox Evaluate("=SUMPRODUCT((TEXT(Venda!$L$3:$L$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$N$3:$N$5000)" & _
                "+SUMPRODUCT((TEXT(Venda!$O$3:$O$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$Q$3:$Q$5000)" & _
                "+SUMPRODUCT((TEXT(Venda!$R$3:$R$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$T$3:$T$5000)")

Why don't you put the formula in a cell and reference the result?

 

[eloirjr]

You are right, the limit is the problem. Now it´s working. I prefer use this way due speed, now I have only 3000 lines, but in the near future we never know.
Last question, to pass value to the variable, the code will be:
currectvalue = Evaluate("=SUMPRODUCT((TEXT(Venda!$L$3:$L$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$N$3:$N$5000)" & _
"+SUMPRODUCT((TEXT(Venda!$O$3:$O$5000,""mmmyyyy"")=TEXT(A9,""mmmyyyy""))*Venda!$Q$3:$Q$5000)")
Thanks

 

[Andrew Poulsom]

Yes that's right.