Determining if a cell/range is in error?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chrisso - 11 May 2008 11:44 GMT

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

Reply to this Message

Dave Peterson - 11 May 2008 12:06 GMT

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

> Hello
>
[quoted text clipped - 8 lines]
> Thanks in advance for any ideas,
> Chrisso

Signature

Dave Peterson

Reply to this Message

Shane Devenshire - 11 May 2008 15:50 GMT

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

> Hello
>
[quoted text clipped - 8 lines]
> Thanks in advance for any ideas,
> Chrisso

Reply to this Message

Chrisso - 12 May 2008 08:02 GMT

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

Chrisso

Reply to this Message