ActiveSheet.Evaluate Problems

G

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!!
 
B

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)
 
G

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 ?
 
B

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)
 
G

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
 
T

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.
 
G

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
 
G

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 ?
 
B

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)
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top