Creating macro

  • Thread starter Thread starter Jamie
  • Start date Start date
J

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.
 
Try

With ActiveCell

.Value2 = "=IF(ISERROR(" & Right$(.Formula, Len(.Formula) - 1) &
"),0," & Right$(.Formula, Len(.Formula) - 1) & ")"
End With
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
Back
Top