hiding formula errors

[anikondr]

is there a way to hide the formula error in the cell.
ex. #VALUE or #DIV/0 as a work in progress?

 

[Mark W.]

Rather than hide 'em why not prevent them?

 

[Ludovico]

Think you might like this macro. Slect the cell or cells you want to apply it to and it converts any formulas within them so that DIV/0 or other error messages will not show:

Sub IFISERROR()
'
' IFISERROR Macro
' Macro recorded 11/7/01 by Ludovico
'


Dim FormulaChanged As String

For Each cell In Selection
FormulaChanged = Mid(cell.Formula, 2)
FormulaChanged = "=IF(ISERROR(" & FormulaChanged & "),""""," & FormulaChanged & _
")"
cell.Formula = FormulaChanged
Next cell
End Sub
This message was edited by Ludovico on 2002-04-17 12:41

 

[Mark O'Brien]

Something like:

=IF(ISERROR(yourstuff), yourstuff, "")

Where "yourstuff" is the formula that may return an error.

(Mark W. I missed out the "=TRUE" part just for you)

I agree that prevention is a better solution though. Proactive not reactive.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-17 12:41

 

[anikondr]

thanks all. however, the only reason the formula generates an error is because the form field is BLANK. Later in the day the user will put in a number into the cell that is referenced in the formula and the error #DIV/0 will disappear. however, i am reluctant showing clients intermediate forms with internal error messages, and cannot do without intermediate printing of those forms.
so changing a formula for every instance where i can anticipate that zero value is not feasible. i am wondering whether this #DIV/0 can be disabled somewhere in excel options.

 

[bobmc]

Try this in C1 (where "A1" would be an item cost, "B1" is a cell awaiting entry from the user for quantity)

IF(B1="","",A1*B1)

Good luck!

 

[Mark W.]

On 2002-04-17 13:43, anikondr wrote:
...so changing a formula for every instance where i can anticipate that zero value is not feasible. i am wondering whether this #DIV/0 can be disabled somewhere in excel options.

I'm not quite sure why =IF(B1,A1/B1,"") is not feasible. To me that's a fundamental design issue. Is 0 in the domain of B1? If so, deal with it.

There's no way that I know of to globally disable any error value.
This message was edited by Mark W. on 2002-04-17 14:47