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?

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



WBTKbeezy wrote:
>
> 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

That is perfect, Dave, Thanks!

The Error Handler is a nice touch as well.

"Dave Peterson" wrote:

> 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
>
>
>
> WBTKbeezy wrote:
> >
> > 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
>