Creating macro
I put in the below formula but keep getting a syntax error.
Sub FixErrors()
Dim Cell As Object, Sh As Object
For Each Cell In ActiveSheet.UsedRange.Cells
If IsError(Cell.Value) Then
Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) -
1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")"
End If
Next Cell
End Sub
"Paul C" wrote:
Use this line of code to change the active cell formula
ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula,
Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula,
Len(ActiveCell.Formula) - 1) & ")"
This would fix all errors on a the active sheet
Sub FixErrors()
Dim Cell As Object, Sh As Object
For Each Cell In ActiveSheet.UsedRange.Cells
If IsError(Cell.Value) Then
Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) -
1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")"
End If
Next Cell
End Sub
--
If this helps, please remember to click yes.
"Jamie" wrote:
Hi,
Can anyone write a macro for me so that it takes the current formula I have
in a cell and adds a IF function and iserror fuction around it.
Here is what I would like:
Current formula =A1/B1
after running the macro I would like it to look like this:
=if(iserror(A1/B1),0,A1/B1)
Thanks for your help.
|