Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Code to Hide Error Formulas (Excel 2000>)

    Any improvements or further suggestions on this code!??

    It is used to hide errors in all calculations that display #Error output on the current sheet!

    <pre>
    Sub HideErrorCalcs()
    Dim ErrRange As Range
    Set ErrRange = Selection.SpecialCells(xlCellTypeFormulas, 16)
    Dim ErrCell As Range
    Dim CellCalc As String
    Dim CharLength As Integer
    For Each ErrCell In ErrRange
    CharLength = Len(ErrCell.Formula) - 1
    CellCalc = Mid(ErrCell.Formula, 2, CharLength)
    ErrCell.Formula = _
    "=IF(ISERROR((" & CellCalc & ")),"""",(" & CellCalc & "))"
    Next ErrCell
    End Sub
    </pre>

    Regards,
    Rudi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code to Hide Error Formulas (Excel 2000>)

    I leave it to you to solve (you seem to like puzzles) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    "Set ErrRange" will give an error if the sheet has not formulas.

    If you run the code multiple times, you will continue to add "iserror(" functions to the formulas until you make formulas too large or that have too many nested ifs.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to Hide Error Formulas (Excel 2000>)

    You don't need CharLength. You can change

    CellCalc = Mid(ErrCell.Formula, 2, CharLength)

    to

    CellCalc = Mid(ErrCell.Formula, 2)

    If you omit the Length argument of Mid(String, Start, Length), Mid returns all characters from String starting at Start.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to Hide Error Formulas (Excel 2000>)

    Yes, I like puzzles...but only in the Puzzle Forum! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Thanx for the advice...I've taken it to heart!
    Cheers
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to Hide Error Formulas (Excel 2000>)

    Tx Legare...I'll have alook at what you have done when I get the chance!
    Cheers.

    Hans,
    Tx for the advice...I was not aware of that ability in the MID Function! That is handy!!! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to Hide Error Formulas (Excel 2000>)

    Hi Legare,
    I'm browsing your code. Is it valid to say "On error goto zero". Should that not be goto "0"???
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to Hide Error Formulas (Excel 2000>)

    That should be "On Error Goto 0" (unless you'd insert a label named zero in the code).

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to Hide Error Formulas (Excel 2000>)

    You are correct, that was air code. I actually noticed it after I made the post and ment to go back and correct it but got distracted. I'm off to make that correction now.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to Hide Error Formulas (Excel 2000>)

    I would modify it slightly:

    <code>
    Sub HideErrorCalcs()
    Dim ErrRange As Range
    On Error Resume Next
    Set ErrRange = Selection.SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0
    Dim ErrCell As Range
    Dim CellCalc As String
    Dim CharLength As Integer
    If Not ErrRange Is Nothing Then
    For Each ErrCell In ErrRange
    If InStr(ErrCell.Formula, "ISERROR") = 0 Then
    CharLength = Len(ErrCell.Formula) - 1
    CellCalc = Mid(ErrCell.Formula, 2, CharLength)
    ErrCell.Formula = _
    "=IF(ISERROR((" & CellCalc & ")),"""",(" & CellCalc & "))"
    End If
    Next ErrCell
    End If
    End Sub
    </code>
    Legare Coleman

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to Hide Error Formulas (Excel 2000>)

    Thanx guys! Your advice is valuable!
    Cheers
    Regards,
    Rudi

Posting Permissions

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