Is there a way to check if a range contains CVErr values

[Tooley]

Hi All,

We currently import files which may have CVErr values included (eg #N/A). When the data comes to us it contains no formula's and we have no control over the user ensuring no errors exist.

Some checks are already being performed such as the following for merged cells:

Please Login or Register  to view this content.

Is there a similar way to check the range as a whole for any of the CVErr values (not by individual cell due to the size of data)?

Thanks

Tooley

[Norie]

Tooley

You could try using SpecialCells to see if there any errors.

It would return all the cells but it doesn't check them one by one.

[Tooley]

Hi Norrie,

I have the following, which whilst doesn't appear to resolve this specific issue, will be really useful elsewhere so have posted if it benefits others:

Please Login or Register  to view this content.

Unless I've done something wrong, the above appears to work if any formulas in the range return a CVError, but not if the range contains no formulas with the errors as values only (eg "#N/A")?

Thanks

Tooley

[xlnitwit]

Change xlCellTypeFormulas to xlCellTypeConstants

[Norie]

Tooley

I'm not sure I follow, do you want to check if a range has any errors or for specific types of errors or if it has no errors?

Please Login or Register  to view this content.

[jindon]

UDF

Please Login or Register  to view this content.

[Tooley]

Thanks both, the following now appears to work as I need it too:

Please Login or Register  to view this content.

The above uses ErrorHandler label for handled errors as I avoid Resume Next as much as possible and I use an ErrorHandling class for the unhandled errors. Also I haven't attempted to set a test range purely down to preference and consistency with other code. If there are more benefits using the Test range approach, please advise as am always looking to improve.

Thanks again both

[Norie]

Nothing wrong with using On Error Resume Next as long as you confine it to handling the error you are trying to deal with rather than using it as some sort of all encompassing error handler/hider.