| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Evaluate Problems
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!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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 ups.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!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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 ups.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 ? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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 ups.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 ? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
On Apr 3, 2:02 pm, Tom Ogilvy
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" 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 ups.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 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
On Apr 3, 4:02 pm, "gmac" wrote:
On Apr 3, 2:02 pm, Tom Ogilvy 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" 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 ups.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 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 ? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveSheet.Evaluate Problems
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" wrote in message oups.com... On Apr 3, 4:02 pm, "gmac" wrote: On Apr 3, 2:02 pm, Tom Ogilvy 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" 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 ups.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 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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying new activesheet after other activesheet is hidden? | Excel Programming | |||
| activesheet | Excel Programming | |||
| ActiveSheet.Name? | Excel Programming | |||
| ActiveSheet | Excel Programming | |||
| ActiveSheet | Excel Programming | |||
![gmac[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
