variable not reading correctly using sumproduct
"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.
|