I spent time scouring the interwebs trying to find a macro that would add an "IFERROR" function to any formula for the cells that are selected. I found some code and modified it to be a little more custom, so apologies in that I don't know who to give credit to.
My issue with the below code is that if only have one cell selected, it loops through every cell in the worksheet and adds an IFERROR 'wrapper' to every formula. There seems like there should be a simple solution so that if you only have a single cell selected, the macro only applies to that cell.
Sub wrap()
'Adds =IfError() around formulas
Dim cel As Range
Dim rng As Range
Dim Check As String
Dim userInput As String
Dim Equ As String
userInput = InputBox("What would you like the cell(s) to display if there's an error?")
Equ = "=IFERROR(_x ,""" & userInput & """)"
On Error Resume Next
Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub
With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub
I appreciate any feedback - thanks!!
Bookmarks