Making the same change to multiple functions at once.

[Cristy]

Hi members,

Is there a way to add the same addition to a formula on
multiple formula's at once?

I have a column of formulas that I would like to add the
IF(ISERROR function to and want to find out if there's an
easy way to update them all rather than doing them one by
one.

The formulas are all different as they're GETPIVOT links
to a pivot table, so unfortunately drag fillling won't
work.

Hope you can help.
Thanks

 

[Mike]

Is there no way of using the replace function (Ctrl+H) somehow?

Highlight the relevant cells, then Ctrl+H, and then on the Replace menu
(ensuring it looks into formulas) and do something such as Find what:
=getpivot and Replace with: =if(iserror(getpivot

 

[J.E. McGimpsey]

Take a look at the reply I gave this morning back when you were "CJ"

If you can't find it in your newsreader, you can always try the
Google archives:

http://groups.google.com/advanced_group_search?q=group:*excel*

Here's your message from this morning, along with my reply:


http://google.com/groups?threadm=6d6401c3b3d6$b38ecf80$a601280a%4
0phx.gbl

If that reply doesn't work for you, please post a reply in the same
thread so that others can see what didn't work.

 

[J.E. McGimpsey]

that will lead to syntax errors, since the IF() and ISERROR()
functions won't have their arguments/closing parens.

You could undoubtedly do it in multiple replaces, but the first
replace should be the = sign to convert the formulae to text. Then
whatever was substituted would have to be replaced with = as a last
step.

 

[Gord Dibben]

Cristy

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 & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Gord Dibben XL2002

 

[Guest]

This was the sort of thinking I needed. Thanks very much.
I'll work on it.

Cheers