That worked great Joe thanks !!! You and Joel were actually both right in the
sense that i was storing the DateTextBox.text as the wrong data type. Still
kind of new to VB/VBA (and programming in general) so i appreciate your help
guys..

"JoeU2004" wrote:

"Lman" wrote:

Try the following:

Dim CustomDate as Double

CustomDate = CDate(DateTextBox.text)

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


You don't say what DateTextBox.text looks like. My guess: m/d/yy, d/m/yy
or yy/m/d.

Consider that date 1/2/09, for example. Then the first term of the
SUMPRODUCT in your formula becomes: --(AM1:AM60000 1/2/09). In that
form, 1/2/09 is a simple arithmetic expression, 1 divided by 2 divided by 9.
I presume that is less than all values in AM1:AM60000. Assuming all the
other conditions are true, that would explain why you get the sum of
BN1:BN60000.

Using CDate() stored into a Double cause the date string to be converted to
a "serial number", the form in which date/time is stored internally in
Excel.


PS: If you do Worksheets("sheet1"), you do not need to do
"sheet1!AM1:AM6000". The corrected Evaluate expression should work fine
either way.


----- original message -----

"Lman" wrote in message
...
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.