Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: hiding formula errors

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    New York
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Rather than hide 'em why not prevent them?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-17 12:41 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    New York
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •