Macro to edit formula

[Tang123]

I have a formula which gives a #n/a in the cell :
=VLOOKUP(A1,B14:C29,2,FALSE)

In order not to show #n/a, I've edited the cell and changed it to:
=IF(ISERROR(VLOOKUP(A1,B14:C29,2,FALSE)),0,VLOOKUP(A1,B14:C29,2,FALSE))

Is there a way to create a macro so that I don't have to edit the
formula every time. In other words, the macro will do all the editing
for me.

Thanks in advance for your help!

 

[Dave Peterson]

One way...

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas in selection"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myStr = Mid(.Formula, 2)
myStr = "=if(iserror(" & myStr & "),""""," & myStr & ")"
.Formula = myStr
End With
Next myCell

End Sub

Select your range of cells to fix and then run this macro.

 

[Tang123]

Thanks Dave.
It works!
Much appreciated.