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.
--
Regards,
Tom Ogilvy
"gmac" wrote:
On Apr 3, 1:40 pm, "Bob Phillips" wrote:
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" wrote in message
ps.com...
On Apr 3, 10:48 am, "Bob Phillips" wrote:
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" wrote in message
roups.com...
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!!
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,
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