VBA: using Evaluate with sumproduct function ( Date Comparison not working)

Hello,

I am relatively new to this forum. Please accept my apologies if i had missed out on any forum rules. Now..

1. i have a vba code which uses evaluate with Sumproduct function and it is (rather...was!) working
2. i tried to expand this by including a date comparison but it is not working
3. Date is stored in a cell which is in turn stored in a variable and i used that variable in the function
4. Xmonth and Xweek (mentined below in code ) are names defined. we can assume Xdate also as same.
5. Val1 / Val2 is declared as variant and it has values from cell and val3 stores date.

Relevant extract from the code
val1=range("i3").value
val2=range("i4").value
val3=range("i5").value

myval=evaluate("=sumproduct((xmonth=""" & val1 &""")*(xweek=""" & val2 &""")

but..if i append this with the following code it doesn't work...i tried multiple options like clng,datevalue etc..it give #name,#value and error message at times.

myval=evaluate("=sumproduct((xmonth=""" & val1 &""")*(xweek=""" & val2 &""")*(xdate=""" & val3 &""")

can someone help me out on this..and please revert for any additional information which is required which can help me in getting a resolution. Thx.

There are parenthesis missing

Please Login or Register  to view this content.

If you want date (without times) use CLng and don't include quotes round the value:

Please Login or Register  to view this content.

Please mark the thread solved then - see FAQ link at the top of the page for details.