Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not reading correctly using sumproduct
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not reading correctly using sumproduct
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not reading correctly using sumproduct
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not reading correctly using sumproduct
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not reading correctly using sumproduct
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT calcs correctly on one PC, but incorrectly on another | Excel Discussion (Misc queries) | |||
sumproduct - reading a variable value | Excel Discussion (Misc queries) | |||
reading comments value into a string variable | Excel Programming | |||
Using SUMPRODUCT...but prolly not correctly | Excel Worksheet Functions | |||
Reading Textfile into a variable | Excel Programming |