Find and Replace?

[rhaney]

Good afternoon!
If you have a worksheet with several iterations of a formula in a column, is there a way to use find and replace to put something at the beginning and end of each of the formulas. For example - putting an iferror statement into an existing group of formulas.

Thank you!
Robert

 

[borgem]

I ran into this issue yesterday and adding an iferror gets to be a little more complicated. What i ended up doing was(using ctrl+h):

replace the first portion of the formula's =if( with '=iferror(if

This way, you don't get hung on the error about not completing the iferror part of the function.

next replace the end of your function using enough of it to make sure it is unique and will only replace what you need
<a5),a3,a1+5), then="" you="" would="" replace="" something="" like="" +5)="" with="" +5),"")<a2),a1+5)="" use="" ctrl+h="" to="" 5)="" 5),"")="" or="" of="" that="" nature.
<a2,a2,a1+2), then="" you="" would="" replace="" +2)="" with="" +2),"")
Now the tricky part...removing the apostrophe which is a hidden character. I found this macro yesterday when I figured out this workaround.

Private Sub ApostroRemove()
    For Each currentcell In Selection
        If currentcell.HasFormula = False Then
            'Verifies that procedure does not change the
            'cell with the active formula so that it contains
            'only the value.
            currentcell.Formula = currentcell.Value
        End If
    Next
End Sub

And that should do it.</a2,a2,a1+2),></a5),a3,a1+5),>

 

[rhaney]

Thank you very much!

Robert