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

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

Thanks Dave.
It works! :)
Much appreciated.


--
Tang123
------------------------------------------------------------------------
Tang123's Profile: http://www.excelforum.com/member.php...o&userid=27846
View this thread: http://www.excelforum.com/showthread...hreadid=473577