VB + array formulas
 

I have the following VB code

Dim cnt as Integer
ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject" "),1,0)")

The above code works fine but when I add the following line

cnt=ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject" "),1,0)")

I get a type mismatch error.

Thanks in Advance

VB + array formulas
 

On 21 Mar 2007 13:09:38 -0700, wrote:

I have the following VB code

Dim cnt as Integer
ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

The above code works fine but when I add the following line

cnt=ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

I get a type mismatch error.


The formula is returning an error and can't fit into an Integer data type.

cnt =
ActiveSheet.Evaluate("SUM(IF(('Data'!$K$7:$K$259< ""Cancelled"")*('Data'!$K$7:$K$259<""Reject""),1, 0))")
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

VB + array formulas
 

Dim the vairable Cnt as Double

"Dick Kusleika" wrote:

On 21 Mar 2007 13:09:38 -0700, wrote:

I have the following VB code

Dim cnt as Integer
ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

The above code works fine but when I add the following line

cnt=ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

I get a type mismatch error.


The formula is returning an error and can't fit into an Integer data type.

cnt =
ActiveSheet.Evaluate("SUM(IF(('Data'!$K$7:$K$259< ""Cancelled"")*('Data'!$K$7:$K$259<""Reject""),1, 0))")
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

VB + array formulas
 

On Mar 21, 7:56 pm, Divakar wrote:
Dim the vairable Cnt as Double

"Dick Kusleika" wrote:
On 21 Mar 2007 13:09:38 -0700, wrote:

I have the followingVBcode

Dim cnt as Integer
ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

The above code works fine but when I add the following line

cnt=ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")

I get a type mismatch error.

The formula is returning an error and can't fit into an Integer data type.

cnt =
ActiveSheet.Evaluate("SUM(IF(('Data'!$K$7:$K$259< ""Cancelled"")*('Data'!$K$7:$K$259<""Reject""),1, 0))")
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

I get the same error even after changing the data type to double

VB + array formulas
 

On 26 Mar 2007 06:56:24 -0700, wrote:

On Mar 21, 7:56 pm, Divakar wrote:
Dim the vairable Cnt as Double


cnt=ActiveSheet.Evaluate ("SUM(IF('Data'!$K$7:$K
$259<""Cancelled"")*(Data'!$K$7:$K$259<""Reject ""),1,0)")


cnt =
ActiveSheet.Evaluate("SUM(IF(('Data'!$K$7:$K$259< ""Cancelled"")*('Data'!$K$7:$K$259<""Reject""),1, 0))")
--
I get the same error even after changing the data type to double

VB will happily convert the result into an Integer, unless it's greater than
32,767 or it's an error. Excel reads and writes cell values as Doubles, so
you could eliminate some type conversion, but that's not what's causing your
error. If you haven't already, change your line of code to the one I
provided and it will eliminate the error.


--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com