Creating macro

[Jamie]

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.

 

[Bob Phillips]

Try

With ActiveCell

.Value2 = "=IF(ISERROR(" & Right$(.Formula, Len(.Formula) - 1) &
"),0," & Right$(.Formula, Len(.Formula) - 1) & ")"
End With

 

[Paul C]

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

 

[Mike H]

Hi,

Try this

r = Mid(Range("c1").Formula, 2)
Range("c1").Formula = "=IF(ISERROR(" & r & "),0," & r & ")"

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Gord Dibben]

Sub ErrorTrapAdd()
Dim mystr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
mystr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Jamie]

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

 

[Jamie]

I figured out the issue. Is there a way to make that macro only work the
cells selected? That would be more helpful.

Thanks - Jamie

 

[Gary Brown]

Try...
For Each Cell In Selection

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Bob Phillips]

For Each cell In Selection

With Cell

. Value2 = "=IF(ISERROR(" & Right$(.Formula, Len(.Formula) - 1) &
_
"),0," & Right$(.Formula, Len(.Formula) - 1) &
")"
End With
Next cell

 

[Gord Dibben]

See my post.


Gord

I figured out the issue. Is there a way to make that macro only work the
cells selected? That would be more helpful.

Thanks - Jamie