Macro to edit formula

  • Thread starter Thread starter Tang123
  • Start date Start date
T

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!
 
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.
 
Back
Top