|
Problem with Evaluate
Hi All,
I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
Andi,
In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
Bernie - It should be in quotes - It is in quotes on my version, I must have
messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
Then I would suspect that the 'switching' of sides is the problem:
In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
Hi Bernie,
I tried your suggestion but still get the same result. I now have the following formula:- Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") Which gives the result #Value! The formula is evaluating the following:- =SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod - Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID)) Which equates to 10 If I try MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV - Pre-Prod - Maintance"")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))") I get a run-tim error type mismatch Not sure if this helps. Andi "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Then I would suspect that the 'switching' of sides is the problem: In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
Andi,
I would try breaking the problem down to find where the error arises. Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")*1)") Evaluate("SUMPRODUCT((" & strTask_Finish & "=" & str_Date & ")*1)") Evaluate("SUMPRODUCT((" & strTask_Type & "=" & ColType & ")*1)") Evaluate("SUMPRODUCT((" & strTask_DCIT & ")*1)") HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi Bernie, I tried your suggestion but still get the same result. I now have the following formula:- Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") Which gives the result #Value! The formula is evaluating the following:- =SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod - Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID)) Which equates to 10 If I try MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV - Pre-Prod - Maintance"")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))") I get a run-tim error type mismatch Not sure if this helps. Andi "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Then I would suspect that the 'switching' of sides is the problem: In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi |
Problem with Evaluate
I do this when I'm struggling with those long formulas:
dim myFormula as string myformula = "sumproduct(......)" debug.print myformula Then I paste it into a cell in the worksheet. I add the leading equal sign and see where excel yells. Then when I have it working, I just evaluate that string: xxx = evaluate(myformula) Andibevan wrote: Hi Bernie, I tried your suggestion but still get the same result. I now have the following formula:- Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") Which gives the result #Value! The formula is evaluating the following:- =SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod - Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID)) Which equates to 10 If I try MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV - Pre-Prod - Maintance"")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))") I get a run-tim error type mismatch Not sure if this helps. Andi "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Then I would suspect that the 'switching' of sides is the problem: In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi -- Dave Peterson |
Problem with Evaluate
Thanks Dave but I have done exactly that and it returns the answer 10.
If I then take that formula, double up any quotation marks as necessary and do msgbox evaluate(formula used in worksheet) throws a "a run-tim error type mismatch" Hence my confusion........ "Dave Peterson" wrote in message ... I do this when I'm struggling with those long formulas: dim myFormula as string myformula = "sumproduct(......)" debug.print myformula Then I paste it into a cell in the worksheet. I add the leading equal sign and see where excel yells. Then when I have it working, I just evaluate that string: xxx = evaluate(myformula) Andibevan wrote: Hi Bernie, I tried your suggestion but still get the same result. I now have the following formula:- Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") Which gives the result #Value! The formula is evaluating the following:- =SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod - Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID)) Which equates to 10 If I try MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV - Pre-Prod - Maintance"")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))") I get a run-tim error type mismatch Not sure if this helps. Andi "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Then I would suspect that the 'switching' of sides is the problem: In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi -- Dave Peterson |
Problem with Evaluate
How about an alternative?
instead of using strings, use ranges... This: Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") becomes... Dim RngTaskStart as range dim rngDate as range set RngTaskStart = worksheets("Working - Dependency Chart V50_V0.4.xls") _ .range("LU_DC_Start") set rngDate = worksheets("Release Schedule (2)").range("A168") (and do the rest, too....) Then myformula="sumproduct(" & rngtaskstart.address(external:=true) _ & "<=" & rngdate.address(external:=true) & ")*(" & ..... The (external:=true) will return the workbook name, worksheet name and address. Then the application.evaluate will work no matter what sheet is active. I find letting excel worry about the syntax for the range addresses much easier to deal with--too many typos can crop up when I'm left to my own devices. Andibevan wrote: Thanks Dave but I have done exactly that and it returns the answer 10. If I then take that formula, double up any quotation marks as necessary and do msgbox evaluate(formula used in worksheet) throws a "a run-tim error type mismatch" Hence my confusion........ "Dave Peterson" wrote in message ... I do this when I'm struggling with those long formulas: dim myFormula as string myformula = "sumproduct(......)" debug.print myformula Then I paste it into a cell in the worksheet. I add the leading equal sign and see where excel yells. Then when I have it working, I just evaluate that string: xxx = evaluate(myformula) Andibevan wrote: Hi Bernie, I tried your suggestion but still get the same result. I now have the following formula:- Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _ "*(" & strTask_Finish & "=" & str_Date & ")*" & _ "(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))") Which gives the result #Value! The formula is evaluating the following:- =SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod - Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID)) Which equates to 10 If I try MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV - Pre-Prod - Maintance"")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))") I get a run-tim error type mismatch Not sure if this helps. Andi "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Then I would suspect that the 'switching' of sides is the problem: In the first, ColType is on the right of the = sign: (" & strTask_Type & " = " & ColType & ") In the third, ColType is on the left: (""" & ColType & """=" & strTask_Type & ") Generally: multicells = onecell, or multicell = constant HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Bernie - It should be in quotes - It is in quotes on my version, I must have messed it up while tranfering it to an e-mail. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Andi, In the first formula, you use " = " & ColType & ") In your third formula, you use: "(""" & ColType & """= So, should ColType be in quotes or not? HTH, Bernie MS Excel MVP "Andibevan" wrote in message ... Hi All, I am having problems constructing a complex evaluate formula that I am using within a user defined function:- This formula works fine:- Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" & strTask_DCIT & "))") This also works:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & "))") But when I try to combine the 2 I get a Value# error:- EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" & str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" & strTask_Type & ")*(" & strTask_DCIT & "))") I can't see what's wrong.... Any ideas Ta Andi -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com