variable not reading correctly using sumproduct

[Lman]

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.

 

[Joel]

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]

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.

 

[JoeU2004]

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 -----

 

[Joel]

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]

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..