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


--

HTH

Bob

"Jamie" <(E-Mail Removed)> wrote in message
news:36CDC944-7118-40C5-8473-(E-Mail Removed)...
> 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.

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.

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.

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 <(E-Mail Removed)>
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.

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.

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.

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.

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" <(E-Mail Removed)> wrote in message
news:F303E50E-C62D-49E4-845E-(E-Mail Removed)...
>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.

See my post.


Gord

On Tue, 6 Apr 2010 11:37:20 -0700, Jamie <(E-Mail Removed)>
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.