urgent type 13 mismatch assistance required!

[nick_clarke]

Hi

I've recently transferred some working vba code from one spreadhseet to another, changing the cell references only to correspond to the new spreadsheet.

The code returns a type 13 mismatch error when I try to run it in the new spreadsheet. It uses evaluate and sumproduct, and I have verified the sumproduct works by entering the formula into a cell.

Below is the code:

e1 = Evaluate("=SUMPRODUCT(('Active Case Tracker'!$T$3:$T$15998>" & twka & " )*('Active Case Tracker'!$T$3:$T$15998<=" & twkb & ")*('Active Case Tracker'!$T$3:$T$15998>'Active Case Tracker'!$Z$3:$Z$15998)*('Active Case Tracker'!$AE$3:$AE$15998=""No Change Of Outcome"")*('Active Case Tracker'!$X$3:$X$15998=""N/A""))")

e1 is a variable that I have declared as an integer, as this was how it was declared in the code for the working spreadsheet.

Any ideas on why I am getting the error message?

My head is starting to hurt from banging it against my desk, any help would be much appreciated.

Thanks

 

[Andrew Poulsom]

Maybe you need to declare e1 as Long (or Double or Variant).

 

[nick_clarke]

Thanks for the quick response.

I tried changing it to long, double and variant, and still got the same type 13 mismatch error.

It's weird because I know the sumproduct formula works as I have tested it within a cell. It appears to fail when I put it into VBA.

I forgot to say in my original post that twka and twkb are variables declared as long that hold date values.

Any futher ideas?

Thanks

 

[Andrew Poulsom]

What's twka and twkb? What would the formula be when on a spreadsheet?

 

[nick_clarke]

Hi

The formula on the spreadsheet is this:

=SUMPRODUCT(('Active Case Tracker'!$T$3:$T$15998>J35 )*('Active Case Tracker'!$T$3:$T$15998<=J36)*('Active Case Tracker'!$T$3:$T$15998>'Active Case Tracker'!$Z$3:$Z$15998)*('Active Case Tracker'!$AE$3:$AE$15998="No Change Of Outcome")*('Active Case Tracker'!$X$3:$X$15998="N/A"))

where the formula for J35 is :

=TODAY()-7

and the formula for J36 is:

=TODAY()-1

It works within the cell and returns the correct value.

Thanks

 

[Andrew Poulsom]

From testing it seems that there are one too many conditions for Evaluate to cope with. It works if you remove any one of them. Sorry I don't know a solution other than putting the formula on a worksheet and returning it's value.

 

[nick_clarke]

Thanks for your help - I'll try removing one of the conditions from evaluate and testing again.

Fingers crossed it works