Problem with Evaluate

[Andibevan]

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

 

[Bernie Deitrick]

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]

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]

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]

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]

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

 

[Dave Peterson]

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]

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]

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.