Insert IFERROR( and ,"-") at the begining and end of formulas?

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
591
Office Version
  1. 365
Platform
  1. Windows
How can I insert IFERROR( and ,"-") at the begining and end of formulas?

With search/replace?

Thanks!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Re: How can I insert IFERROR( and ,"-") at the begining and end of formulas?

something like ASAP utilities, if when you do it, you also add ' (i.e. =becomes '=IFERROR( as the first character then it will look like text until you have finished changing, then remove the leading '
 
Upvote 0
Re: How can I insert IFERROR( and ,"-") at the begining and end of formulas?

If none of your cells already have an IFERROR formula you can test the below amended Chris Newman code.
Just select the cells and run the macro.

Code:
Sub WrapIfError()

'PURPOSE: Add an IFERROR() Function around all the selected cells' formulas
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range
Dim cell As Range
Dim x As String

'Determine if a single cell or range is selected
  If Selection.Cells.Count = 1 Then
    Set rng = Selection
    If Not rng.HasFormula Then GoTo NoFormulas
  Else
    'Get Range of Cells that Only Contain Formulas
      On Error GoTo NoFormulas
        Set rng = Selection.SpecialCells(xlCellTypeFormulas)
      On Error GoTo 0
  End If

'Loop Through Each Cell in Range and add =IFERROR([formula],"-")
  For Each cell In rng.Cells
    x = cell.Formula
    cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & "-" & Chr(34) & ")"
  Next cell

Exit Sub

'Error Handler
NoFormulas:
  MsgBox "There were no formulas found in your selection!"

End Sub
 
Upvote 0
Thanks but can you give me the exact pairs for search/replace?

Thanks
 
Upvote 0
Thanks but can you give me the exact pairs for search/replace?

Thanks

If you select your formulas and run Marks code then you don't need to do anything else
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top