I just notice that evaluate doesn't need the equal sing in front of
SUMPRODUCT. the code may not be working if the dates specified are strings
and not real dates. Does EndofMonth return a string or a number (date)? You
need a number for the code to work. Maybe putting datevalue into the
instruction will help if it is a string.

monthendretail = Worksheets("sheet1").Evaluate("=SUMPRODUCT(--(Am1:Am60000"
_ & datevalue(EndOfMonth) & "),--(AC1:AC60000 = ""P""),--(AQ1:AQ60000 =
""Y""),--(Z1:Z60000 _ = ""N""),Bn1:Bn60000)")


"Lman" wrote:

thanks for your quick reply....

Unfortunatley it produces the same result plus i am using
worksheets(sheet1).evaluate everywhere else in my project and it works fine
for example:

monthendretail = Worksheets("sheet1").Evaluate("=SUMPRODUCT(--(Am1:Am60000"
_ & EndOfMonth & "),--(AC1:AC60000 = ""P""),--(AQ1:AQ60000 =
""Y""),--(Z1:Z60000 _ = ""N""),Bn1:Bn60000)")

Note: EndOfMonth in this case is a function that was created to calculate
the last date of the month not a variable.





"Joel" wrote:

Yo have to specifyu the worksheet in the formula, not using
worhseet(Sheet1").evaluate

stagedtotal = Evaluate("=SUMPRODUCT(--(sheet1!Am1:Am60000 _
" & CustomDate & "),--(sheet1!AC1:AC60000 = ""P""),--(sheet1!AQ1:AQ60000 _ =
""Y""),sheet1!Bn1:Bn60000)")


"Lman" wrote:

I am using the following code in my project.

Dim CustomDate as String

CustomDate = DateTextBox.text

stagedtotal = Worksheets("sheet1").Evaluate("=SUMPRODUCT(--(Am1:Am60000 _
" & CustomDate & "),--(AC1:AC60000 = ""P""),--(AQ1:AQ60000 _ =
""Y""),Bn1:Bn60000)")

However it seems to be returning the CustomDate value incorrectly. no matter
what date is entered it returns the total value of BN1:BN60000 (well the
total within the other parameters set in the formula). However if i change
to < it returns a total of 0. I am thinking it must not be reading the
CustomDate at all, or reading it as 0 Any help is much appreciated.