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 wrote:
>
> 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!
>
> --
> Tang123
> ------------------------------------------------------------------------
> Tang123's Profile: http://www.excelforum.com/member.php...o&userid=27846
> View this thread: http://www.excelforum.com/showthread...hreadid=473577

--

Dave Peterson

[Tang123]

Thanks Dave.
It works!
Much appreciated.