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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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!!
>

On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> >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 ?

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> >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 ?
>

On Apr 3, 1:40 pm, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> >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

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" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> > > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
> >
> > >>news:(E-Mail Removed)...
> >
> > >> >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
>
>

On Apr 3, 2:02 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> 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" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> > > >>news:(E-Mail Removed)...
>
> > > >> >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

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

On Apr 3, 4:02 pm, "gmac" <cug...@gmail.com> wrote:
> On Apr 3, 2:02 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> wrote:
>
>
>
> > 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" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> > > >news:(E-Mail Removed)...
>
> > > > > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> > > > >>news:(E-Mail Removed)...
>
> > > > >> >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
>
> 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 ?

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 3, 4:02 pm, "gmac" <cug...@gmail.com> wrote:
>> On Apr 3, 2:02 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
>> wrote:
>>
>>
>>
>> > 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" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>>
>> > > >news:(E-Mail Removed)...
>>
>> > > > > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>>
>> > > > >>news:(E-Mail Removed)...
>>
>> > > > >> >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
>>
>> 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 ?
>

On Apr 5, 4:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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" <cug...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Apr 3, 4:02 pm, "gmac" <cug...@gmail.com> wrote:
> >> On Apr 3, 2:02 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> >> wrote:
>
> >> > 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" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> >> > > >news:(E-Mail Removed)...
>
> >> > > > > On Apr 3, 10:48 am, "Bob Phillips" <bob....@somewhere.com> 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" <cug...@gmail.com> wrote in message
>
> >> > > > >>news:(E-Mail Removed)...
>
> >> > > > >> >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
>
> >> 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 ?

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