Evaluate Function isn't Returning Errors

[antman2988]

I'm trying to evaluate the formulas in a table object to check for any errors, and I'm using the Evaluate function to evaluate the formulas and check for any errors. Is there a better or different way of doing this? Thanks!

programErrors = Application.Evaluate("COUNTIF(" & progRng.Address & ",IsError)")

 

[RoryA]

That formula is invalid. You could use something like:

programErrors = progRng.Worksheet.Evaluate("SUMPRODUCT(--(ISERROR(" & progRng.Address & ")))")

 

[antman2988]

Ahhhh I see what I did wrong. Thank you, Rory! I appreciate the help as usual. Is "Application" not necessary? I always thought it was.

 

[antman2988]

That formula is invalid. You could use something like:

programErrors = progRng.Worksheet.Evaluate("SUMPRODUCT(--(ISERROR(" & progRng.Address & "))")

Also, I'm not familiar with the dashes. What are they for?

 

[RoryA]

They coerce the resulting True/False values to 1 and 0

 

[antman2988]

They coerce the resulting True/False values to 1 and 0

Also, after testing the new code, I get a mismatch type error. Do you know why this is occurring? Thanks!

 

[RoryA]

How is programErrors declared?

 

[antman2988]

Dim programErrors As Long
Dim progRng As Range

Is it supposed to be a string?

 

[antman2988]

The progRng is set to a

.DataBodyRange

. Could that be the issue?

 

[Fluff]

It's missing a closing bracket, try

programErrors = progRng.Worksheet.Evaluate("SUMPRODUCT(--(ISERROR(" & progRng.Address & ")))")