VB + array formulas

[cugmac]

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

 

[Dick Kusleika]

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

 

[Divakar]

Dim the vairable Cnt as Double

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

 

[cubhasker]

Dim the vairable Cnt as Double

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

 

[Dick Kusleika]

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.