Macro for Iferror

[maheshrk81]

Hi All,

I have a sheet with soo many formulas. but now i need to edit all the formulas with IFERROR, bcoz in few place i am getting #NA or #DVD. Now i need to change those values as 0(zero) with IFERROR formula.

I am using this below code but that is makin my cell empty.
Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

Dim cel As Range
Dim rng As Range
Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub

With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub

Kindly help me out..

Please....

Mahi

 

[maheshrk81]

Please help me out!!!!!!!!!!!!!!11

 

[moonfish]

Please be a bit more patient next time, 12 minutes is hardly a realistic response time.

Anyhow, see if this works better:

Sub ErrorTrapAddDDL()

    ' Adds =If(IsError() around formulas

    Dim cel As Range
    Dim rng As Range
    Dim Check As String

    Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

    Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

    On Error Resume Next

    Set rng = Selection.SpecialCells(xlFormulas, 23)
    If rng Is Nothing Then Exit Sub

    With WorksheetFunction
        For Each cel In rng
            If Not cel.Formula Like Check Then
                cel.Formula = .Substitute(Equ, "_x", Right(cel.Formula, Len(cel.Formula) - 1))
            End If
        Next
    End With
End Sub

 

[Norie]

That code works fine for me, wrapping the existing formulas in ISERROR etc.

You say you now get empty cells?

Are you sure they are empty and it's not that the new formula is actully returning ""?

 

[maheshrk81]

Am sorry for being in hurry......
The code is not working... Its not yet all performing anything.
I need zero in the place of #NA and #DVD
Please help me..

 

[Norie]

If you remove On Error Resume Next what errors, if any, do you get?

 

[maheshrk81]

I have checked with that but not performing...

The First qoute is working fine but that displays the empty cell whereas i need 0(zero) in that place.
Means where ever the formula show as #NA,#DVD then it should show 0 (zero) at that palce.

 

[moonfish]

Oh, that's what you meant. Replace:

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

With:

Const Equ As String = "=IF(ISERROR(_x) ,0 , _x)"

 

[Norie]

So the code is working, but your formula is wrong?

Moonfish's change should fix that.

 

[maheshrk81]

Thanks a lot... its working fine.