Hi there

I have a sheet where some cells have (quite long) formulas in them.
Sometimes, if the cells that they refer to are empty, the formula
cells will have #VALUE! and #N/A errors in them. I could use something
like =IF(ISERROR(formula),"",formula) to show a blank cell if there's
an error but the problem is that some of the formulas are very long
and using this method will just make them much longer.

Is there any way that I can use the WorsheetChange event (or anything
else in VBA event-related) to constantly check cells to see if they
have errors or not and if they do, to just make the cell blank.

You may be able to use the worksheet_calculate event (since you're using
formulas). But as soon as you replace the formula that returns the error with a
"" (or clearing the contents), then the formula is gone.

Personally, I think your solution to use:
=if(iserror(yourformula),"",yourformula)
is what I'd use.

But an alternative that won't replace the error and won't change the formula is
to use format|conditional formatting (xl2003 menus).

Select the range to "fix" (say A1:B9):
with A1 the activecell
Format|conditional formatting
Formula is:
=iserror(a1)
and give it a format that hides the error (white font on white fill???).

The cell still holds the error -- so you'll have to watch out in other
calculations, but it'll look pretty.

wrote:

Hi there

I have a sheet where some cells have (quite long) formulas in them.
Sometimes, if the cells that they refer to are empty, the formula
cells will have #VALUE! and #N/A errors in them. I could use something
like =IF(ISERROR(formula),"",formula) to show a blank cell if there's
an error but the problem is that some of the formulas are very long
and using this method will just make them much longer.

Is there any way that I can use the WorsheetChange event (or anything
else in VBA event-related) to constantly check cells to see if they
have errors or not and if they do, to just make the cell blank.

--

Dave Peterson

On Apr 26, 7:40 pm, Dave Peterson wrote:
You may be able to use the worksheet_calculate event (since you're using
formulas). But as soon as you replace the formula that returns the error with a
"" (or clearing the contents), then the formula is gone.

Personally, I think your solution to use:
=if(iserror(yourformula),"",yourformula)
is what I'd use.

But an alternative that won't replace the error and won't change the formula is
to use format|conditional formatting (xl2003 menus).

Select the range to "fix" (say A1:B9):
with A1 the activecell
Format|conditional formatting
Formula is:
=iserror(a1)
and give it a format that hides the error (white font on white fill???).

The cell still holds the error -- so you'll have to watch out in other
calculations, but it'll look pretty.

wrote:

Hi there

I have a sheet where some cells have (quite long) formulas in them.
Sometimes, if the cells that they refer to are empty, the formula
cells will have #VALUE! and #N/A errors in them. I could use something
like =IF(ISERROR(formula),"",formula) to show a blank cell if there's
an error but the problem is that some of the formulas are very long
and using this method will just make them much longer.

Is there any way that I can use the WorsheetChange event (or anything
else in VBA event-related) to constantly check cells to see if they
have errors or not and if they do, to just make the cell blank.

--

Dave Peterson

Hmmm, that actually works pretty well, thank you!