ActiveSheet.Evaluate Problems

[gmac]

I have the following code in VB

ActiveSheet.Evaluate("SUM(IF((('Sheet2'!$CU$7:$CU$4978=2007)*
('Sheet2'!$c$7:$c$4978= "Dubru") * ('Sheet2'!$aq$7:$aq$4978=
"Chemical") * ('Sheet2'!$ar$7:$ar$4978= "xyz") ),IF('Sheet2'!$K$7:$K
$4978="Not Sent",'Sheet2'!$G$7:$G$4978,0),0))")

When VB executes this code , I get a Type mismatch error. However,
when I execute it as an array formula in excel I get no error but a
result of 0.

Can anyone please try to resolve this issue.

Thanks!!

 

[Bob Phillips]

sFormula = "SUM(IF((('Sheet2'!$CU$7:$CU$4978=2007)*" & _
"('Sheet2'!$c$7:$c$4978= ""Dubru"")*" & _
"('Sheet2'!$aq$7:$aq$4978=""Chemical"")*" & _
"('Sheet2'!$ar$7:$ar$4978= ""xyz"") )," & _
"IF('Sheet2'!$K$7:$K$4978=""Not
Sent"",'Sheet2'!$G$7:$G$4978,0),0))"
ActiveSheet.Evaluate(sFormula)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gmac]

sFormula = "SUM(IF((('Sheet2'!$CU$7:$CU$4978=2007)*" & _
"('Sheet2'!$c$7:$c$4978= ""Dubru"")*" & _
"('Sheet2'!$aq$7:$aq$4978=""Chemical"")*" & _
"('Sheet2'!$ar$7:$ar$4978= ""xyz"") )," & _
"IF('Sheet2'!$K$7:$K$4978=""Not
Sent"",'Sheet2'!$G$7:$G$4978,0),0))"
ActiveSheet.Evaluate(sFormula)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks Bob your response. I have it set up like what you have
specified . I have a string called temp to which I keep on appending
the conditions but for a only difference. I do not append _ at the end
of & . Does it make any difference ?

 

[Bob Phillips]

No, it shouldn't, but I find it easier to read to split it over multiple
lines.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gmac]

No, it shouldn't, but I find it easier to read to split it over multiple
lines.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

Iam getting the same error even after following your suggestion.. I
guess this error is coming when the array formula returns no results.
Any ideas how I can check it before calling the evaluate function

 

[Tom Ogilvy]

I tested it in the immediate window, and it returned a 0

sFormula = "SUM(IF((('Sheet2'!$CU$7:$CU$4978=2007)*" & _
"('Sheet2'!$c$7:$c$4978= ""Dubru"")*" & _
"('Sheet2'!$aq$7:$aq$4978=""Chemical"")*" & _
"('Sheet2'!$ar$7:$ar$4978= ""xyz"") )," & _
"IF('Sheet2'!$K$7:$K$4978=""Not
Sent"",'Sheet2'!$G$7:$G$4978,0),0))"
? evaluate(sFormula)
0


I suspect you are not using the exact same formula and it is returning an
error value which causes the type mismatch results.

 

[gmac]

I tested it in the immediate window, and it returned a 0

sFormula = "SUM(IF((('Sheet2'!$CU$7:$CU$4978=2007)*" & _
"('Sheet2'!$c$7:$c$4978= ""Dubru"")*" & _
"('Sheet2'!$aq$7:$aq$4978=""Chemical"")*" & _
"('Sheet2'!$ar$7:$ar$4978= ""xyz"") )," & _
"IF('Sheet2'!$K$7:$K$4978=""Not
Sent"",'Sheet2'!$G$7:$G$4978,0),0))"
? evaluate(sFormula)
0

I suspect you are not using the exact same formula and it is returning an
error value which causes the type mismatch results.

Iam using the same query. Infact iam writing this query to the excel
before executing it with ActiveSheet.Evaluate(sFormula) . When I run
this query just by appending "=" at the front using array formula I
get a value of 0. This evaluate seems to be failing whenever it
fetches 0 as the result

 

[gmac]

Iam using the same query. Infact iam writing this query to the excel
before executing it with ActiveSheet.Evaluate(sFormula) . When I run
this query just by appending "=" at the front using array formula I
get a value of 0. This evaluate seems to be failing whenever it
fetches 0 as the result

Can anyone please suggest ? Can I check for this error before I
execute the activesheet.evaluate call ?

 

[Bob Phillips]

As Tom said, we don't get an error.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[gmac]

As Tom said, we don't get an error.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

It is not the problem with the Arrayformula but with excel limitation
of executing Array formula with more than 255 chars