Auto IsError

[Guest]

Hello:

I found this code online from Microsoft that automatically changes a formula
to an ISERROR formula using your original formula...

http://support.microsoft.com/kb/213387

It works awesome, but it only does one cell at a time. I have been trying,
with little success, to make it work so I can select a section and it will
work through the whole thing. Can anyone provide assistance?

 

[Dave Peterson]

This is the suggested code:

Sub ErrorToZero()
X = Right(ActiveCell.Formula,Len(ActiveCell.Formula)-1)
ActiveCell.Formula = "=IF(ISERROR(" & X & "),0," & X & ")"
End Sub

I'd use:

Option Explicit
Sub ErrorToZero2()
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
myStr = Mid(myCell.Formula, 2)
myCell.Formula = "=If(iserror(" & myStr & "),0," & myStr & ")"
Next myCell

End Sub

 

[Guest]

That is perfect, Dave, Thanks!

The Error Handler is a nice touch as well.