Determining if a cell/range is in error?

[Chrisso]

Hello

Is there a simple way of determining if a cell/range is in error?

That is, able to tell if it's value is showing "#REF!" or "#NAME!" or
"#VALUE?".

I want to be able to ignore such cells with a universal test. Is this
possible?

Thanks in advance for any ideas,
Chrisso

 

[Dave Peterson]

dim myCell as range
set mycell = activecell

if iserror(mycell.value) then
'it's some kind of error
else
'it's not an error
end if

 

[Shane Devenshire]

Hi,


In addition to the VBA function IsError you have the spreadsheet functions:

=ISERROR(A1)
=ISERR(A1)
=ISNA(A1)

which can be employed in the VBE.

And you can use the Find Method or the Go To command to locate errors:

Selection.SpecialCells(xlCellTypeFormulas, 16).Select

It all depends on what you are trying to do.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Chrisso]

Ahhh - thanks to you both. Just what I needed.

Chrisso