Creating macro
 

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.

Creating macro
 

Try

With ActiveCell

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


--

HTH

Bob

"Jamie" wrote in message
...
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.

Creating macro
 

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.

Creating macro
 

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.


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

Creating macro
 

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

On Tue, 6 Apr 2010 10:48:04 -0700, 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.

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.

Creating macro
 

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

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

Creating macro
 

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



"Jamie" wrote:

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

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

Creating macro
 

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


--

HTH

Bob

"Jamie" wrote in message
...
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

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

Creating macro
 

See my post.


Gord

On Tue, 6 Apr 2010 11:37:20 -0700, Jamie
wrote:

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

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