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.