| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| evaluate | Excel Worksheet Functions | |||
| Formula Will Not Evaluate | Excel Worksheet Functions | |||
| evaluate a function | Excel Programming | |||
| Evaluate or WorksheetFunction Problem | Excel Programming | |||
| using Evaluate on a Name object? | Excel Programming | |||
![Andibevan[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
