Add ISERROR function in your formula using VBA.

One simple procedure that will help you in putting/
removing ISERROR function in your formula.
Procedure:
-----------------------------------------------------------
Public Function AddOrRemoveIsError(AddOrRemove As String, targetRange
As Range, onErrorPrint As String)
Dim cRng As Range
Dim strOldFormula As String, strNewFormula As String
Dim printValueIsNumber As Boolean
If UCase(AddOrRemove) = "ADD" Or UCase(AddOrRemove) = "REMOVE"
Then
Else
MsgBox "Not a valid type"
Exit Function
End If
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'check if printed string is number
If IsNumeric(onErrorPrint) Then
printValueIsNumber = True
End If
'loop through cells and add remove iserror function
For Each cRng In targetRange
'check if current cell has formula
If cRng.HasFormula Then
'store the old formula
strOldFormula = Right(cRng.Formula, Len(cRng.Formula) - 1)
If UCase(AddOrRemove) = "ADD" Then
If InStr(UCase(cRng.Formula), "ISERROR") = 0 Then
If printValueIsNumber = True Then
strNewFormula = "=IF(ISERROR(" & strOldFormula &
")," & onErrorPrint & _
"," & strOldFormula & ")"
Else
strNewFormula = "=IF(ISERROR(" & strOldFormula &
"),""" & onErrorPrint & _
"""," & strOldFormula & ")"
End If
cRng.Formula = strNewFormula
End If
Else
If InStr(UCase(cRng.Formula), "ISERROR") <> 0 Then
If printValueIsNumber = True Then
strNewFormula = "=" & Mid(strOldFormula, InStr(1,
strOldFormula, "," & _
onErrorPrint & ",") + Len("," & onErrorPrint &
","), Len(strOldFormula) - InStr(1, strOldFormula, "," & _
onErrorPrint & ",") - Len("," & onErrorPrint &
",")) & ""
Else
strNewFormula = "=" & Mid(strOldFormula, InStr(1,
strOldFormula, ",""" & _
onErrorPrint & """,") + Len(",""" & onErrorPrint
&
""","), _
Len(strOldFormula) - InStr(1, strOldFormula,
","""
& onErrorPrint & """,") - _
Len(",""" & onErrorPrint & """,")) & ""
End If
cRng.Formula = strNewFormula
End If
End If
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function
--------------------------------------------------
How to use :
Copy above procedure and paste in a VBA module
Now, copy the below code and adjust it as per your requirement. In
below code the first call will put ISERROR function in range A1 to D3
and second call will remove it(in case you want to revert).
Sub test()
Call AddOrRemoveIsError("Add", Range("A1:D3"), "-") ' To add isError
Call AddOrRemoveIsError("Remove", Range("A1:D3"), "-") ' To remove
isError
End Sub