|
SUMPRODUCT troubles
I've looked through the various posts concerning this, but still can't quite
get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. |
SUMPRODUCT troubles
"Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. |
SUMPRODUCT troubles
"Luke" wrote:
I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. |
SUMPRODUCT troubles
Rats, it's still not working. To answer your questions:
1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . |
SUMPRODUCT troubles
"Luke" wrote:
Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . |
SUMPRODUCT troubles
AH, THANK YOU!
The only thing I changed was just leaving ClientNum as is instead of using Format, since all values of ClientNum are greater than 1000 and, as such, would not have any leading 0's. So, really, all that was necessary was to add ".Address" to each of my ranges. Just to further my education, why is that necessary here (i.e., with SUMPRODUCT) and not in other places where I refer to those ranges? Many, many thanks. "Joe User" wrote: "Luke" wrote: Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . . |
SUMPRODUCT troubles
One more question, if you don't mind. What if I need to add one more test to
the sumproduct function? Can you give me an idea how to structure the quotes? Say, for example, I also needed to check whether range ClaimRec equalled "Y". Can you give me an idea how that would look? I changed all my other 2-condition sumproducts in my code and they work fine (thank you), but placing the quotes just right is giving me fits when trying to add a third condition. "Joe User" wrote: "Luke" wrote: Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . . |
SUMPRODUCT troubles
"Luke" wrote:
AH, THANK YOU! The only thing I changed was just leaving ClientNum as is instead of using Format, since all values of ClientNum are greater than 1000 You're welcome. Glad it worked for you. I forgot to mention what you need to do if ClaimClient contains actual numbers, not strings. But apparently my presumption was right. So there is no need to complicate things further. So, really, all that was necessary was to add ".Address" to each of my ranges. [...] why is that necessary here Because you are trying to construct a string of the form "SUMPRODUCT(($A$1:$A$100="1234")*...)". ClaimClient is a Range object in VBA. The property ClaimClient.Address is the string "$A$1:$A$100". not in other places where I refer to those ranges? That depends on the context. In the context WorksheetFunction.Sumproduct(ClaimClient,...), you are pass the Range object, not the address range, to Sumproduct. Likewise in the context Set ClaimClient=Range("a1:a100"). In the context Range("x1:x100")=ClaimClient, you are implicitly refering to the .Value property of both range objects, left and right of the assignment operator. ----- original message ----- "Luke" wrote in message ... AH, THANK YOU! The only thing I changed was just leaving ClientNum as is instead of using Format, since all values of ClientNum are greater than 1000 and, as such, would not have any leading 0's. So, really, all that was necessary was to add ".Address" to each of my ranges. Just to further my education, why is that necessary here (i.e., with SUMPRODUCT) and not in other places where I refer to those ranges? Many, many thanks. "Joe User" wrote: "Luke" wrote: Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . . |
SUMPRODUCT troubles
"Luke" wrote:
I also needed to check whether range ClaimRec equalled "Y". Can you give me an idea how that would look? When constructing formulas as strings in VBA, I find it works best to write the Excel formula -- and even to test it first using Excel. Then edit it the formula to meet the VBA syntax requirements, depending on the context. As you said, the number of adjacent double-quotes can be confusing. So you might write the following Excel formula: =SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat="O" )*(ClaimRec="Y")) Now add the requisite double-quotes and ampersands to satisfy VBA, converting any double-quotes above to a pair of double-quotes in VBA. Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=" & ClaimNum & ")*(" & _ ClaimStat & "=""O"")*(" & ClaimRec.Address & "=""Y""))") BTW, another way to write the original Excel formula is: =SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&Cla imRec="OY")) The VBA syntax becomes: Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=" & ClaimNum & ")*(" _ ClaimStat.Address & "&" & ClaimRec.Address & "=""OY""))") ----- original message ----- "Luke" wrote in message ... One more question, if you don't mind. What if I need to add one more test to the sumproduct function? Can you give me an idea how to structure the quotes? Say, for example, I also needed to check whether range ClaimRec equalled "Y". Can you give me an idea how that would look? I changed all my other 2-condition sumproducts in my code and they work fine (thank you), but placing the quotes just right is giving me fits when trying to add a third condition. "Joe User" wrote: "Luke" wrote: Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . . |
SUMPRODUCT troubles
PS....
I wrote: BTW, another way to write the original Excel formula is: =SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&Cla imRec="OY")) But I wouldn't recommend it(!). Not surprisingly, that form is slower than the other form. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "Luke" wrote: I also needed to check whether range ClaimRec equalled "Y". Can you give me an idea how that would look? When constructing formulas as strings in VBA, I find it works best to write the Excel formula -- and even to test it first using Excel. Then edit it the formula to meet the VBA syntax requirements, depending on the context. As you said, the number of adjacent double-quotes can be confusing. So you might write the following Excel formula: =SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat="O" )*(ClaimRec="Y")) Now add the requisite double-quotes and ampersands to satisfy VBA, converting any double-quotes above to a pair of double-quotes in VBA. Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=" & ClaimNum & ")*(" & _ ClaimStat & "=""O"")*(" & ClaimRec.Address & "=""Y""))") BTW, another way to write the original Excel formula is: =SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&Cla imRec="OY")) The VBA syntax becomes: Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=" & ClaimNum & ")*(" _ ClaimStat.Address & "&" & ClaimRec.Address & "=""OY""))") ----- original message ----- "Luke" wrote in message ... One more question, if you don't mind. What if I need to add one more test to the sumproduct function? Can you give me an idea how to structure the quotes? Say, for example, I also needed to check whether range ClaimRec equalled "Y". Can you give me an idea how that would look? I changed all my other 2-condition sumproducts in my code and they work fine (thank you), but placing the quotes just right is giving me fits when trying to add a third condition. "Joe User" wrote: "Luke" wrote: Rats, it's still not working. Exactly what does not work? Please post the altered statement(s) that you tried. I offered several to try. 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I mentioned before. The ClaimClients range contains 4-digit numbers When people write such things, I become very suspicious. Does ClaimClients contain numbers per se, perhaps with the Custom format "0000" without quotes? Or does ClaimClients contain 4-character strings of digits? I presume you mean the latter. But even if you do as well, it would behoove you use the temporary formula =TYPE(ClaimClients), copied parallel to the entire range, to be sure that all of the range is the expected type, be it numeric (1) or text (2). 2) ClientNum is a VBA defined variable (Dim'd as Integer). Then simply writing ClientNum in the VBA expression does not ensure that you get a 4-character string. Note that the string "0012" is not equal to the string "12". Try the following: ActiveCell.Offset(0, 10).Value = _ ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _ """)*(" & ClaimStat.Address & "=""O""))") All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. Me, too. Copy-and-paste from this message to the VBA editing pane. ----- original message ----- "Luke" wrote in message ... Rats, it's still not working. To answer your questions: 1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using "Set", as you assumed. The ClaimClients range contains 4-digit numbers and the ClaimStat range contains O's and C's. 2) ClientNum is a VBA defined variable (Dim'd as Integer). All those quotation marks cross my eyes. If you can help me get them in the right spot I'll sure appreciate it. "Joe User" wrote: "Luke" wrote: I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. [Sorry about the premature posting. Hit the wrong "button".] Are those named Excel objects, or are they the names of VBA variables? If they are VBA variables, what are their definitions and how are their values assigned? Presumably: Dim ClaimClients as Range, ClaimStat as Range Set ClaimClients = Range("a1:a10") Set ClaimStat = Range("b1:b10") If that is the case, then you should use ClaimClients.Address and ClaimStat.Address in the Evaluate string. Failure to do so would cause a Type Mismatch error. But you might have other errors, which, at the very least, might cause the Evaluate function to fail to deliver the intended result. It is unclear what type ClientNum is and how its value was assigned. Your code snippet treats the value of ClientNum as a string. But is that truly your intent? Is that the type of values in the cells represented by the range variable ClaimClients? Also, at a minimum, you need "--" before each SUMPRODUCT parameter. In summary, the following might be what you intended: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _ ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))") FYI, that can also be written: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))") However, if ClaimClients, ClaimStat and pehaps even ClientNum are named Excel objects, you should write: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=ClientNum)*(ClaimStat=""O""))") Or if ClientNum is a VBA variable: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ "ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))") PS: You could write simply Evaluate instead of ActiveSheet.Evaluate. If none of those comments help, please post more VBA context in your response, and address the questions I raised above. ----- original message ----- "Luke" wrote in message ... I've looked through the various posts concerning this, but still can't quite get this to work. I have a ranges named ClaimClients and ClaimStat and a value named ClientNum. I want to find the number of times the ClaimClients range has a value of ClientNum and the ClaimStat range has a value of "O". Here is the code at present: ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _ ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))") With all the various things I've tried to make this work, I always get either a Type Mismatch error or a Syntax error. . . |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com