Evaluate with SUMPRODUCT and variable

[TG2812]

Hi Everyone!

I'm attempting to calculate the below formula in VBA. However, it returns a type mistmatch error.
Any help would be greatly appreciated =)

dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*($D$2:$D$1000=D" & rw & ")*(MONTH($E$2:$E$1000)=MONTH(E" & rw & "))*(YEAR($E$2:$E$1000)=YEAR(E" & rw & ")))")

 

[Fluff]

What is the value of rw?

 

[TG2812]

Hi Fluff, thank you for your quick reply.

Value of rw is dynamic and is based on the last row of the worksheet.

rw = Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Row

 

[Marcelo Branco]

Be sure the dates in column E are real dates (numbers), not texts that look like dates.

M.

 

[TG2812]

I already tried and the ISNUMBER function returns True. Therefore it is correct.

I'm trying to replicate the below formula in VBA. It works as a formula but fails to execute when turned into a code..
The row in bold type represents the variable rw.

Any idea what is wrong?

=SUMPRODUCT(($H$2:$H$100)*($A$2:$A$100=A2)*($B$2:$B$100=B2)*($D$2:$D$100=D2)*(MONTH($E$2:$E$100)=MONTH(E2))*(YEAR($E$2:$E$100)=YEAR(E2)))

 

[Marcelo Branco]

With real dates in column E this worked for me

Sub aTest()
    Dim rw As Long, dblMyVal As Double
    
    rw = 2
    dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*" _
     & "($D$2:$D$1000=D" & rw & ")*(MONTH($E$2:$E$1000)=MONTH(E" & rw & "))*(YEAR($E$2:$E$1000)=YEAR(E" & rw & ")))")
    MsgBox dblMyVal
End Sub

M.

 

[Fluff]

Do any of your columns have errors?

 

[TG2812]

Thank you Marcelo Branco.
Unfortunately it still does not work. I suspect the activeworksheet to be the root cause...let me explain;

When the code is being executed, I'm showing the worksheet "Main" while the formula refers to the worksheet "Tracking".

Is this an issue ?

 

[TG2812]

Do any of your columns have errors?

No Fluff, the worksheet "Tracking" does not contain any error.

 

[Marcelo Branco]

Thank you Marcelo Branco.
Unfortunately it still does not work. I suspect the activeworksheet to be the root cause...let me explain;

When the code is being executed, I'm showing the worksheet "Main" while the formula refers to the worksheet "Tracking".

Is this an issue ?

See if this works

Sub aTest()
    Dim rw As Long, dblMyVal As Double
    
    rw = 2
    dblMyVal = [COLOR=#ff0000]Sheets("Tracking")[/COLOR].Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*" _
     & "($D$2:$D$1000=D" & rw & ")*(MONTH($E$2:$E$1000)=MONTH(E" & rw & "))*(YEAR($E$2:$E$1000)=YEAR(E" & rw & ")))")
    MsgBox dblMyVal
End Sub

M.