Excel VBA and Finance World

Hi All, I came across a wonderful macro where you can change the formula of a give cell or selection if it gives you error values like #DIV!, #Value! etc. This is a very effective macro for files or excel models where you have to deal with lot of formulas and calculation that affects your output. Below are the steps for the file:


Save this file on your desktop and then open it


Open the model where you want to use the ISERROR formula


Highlight the range you want to use the formula


Hit Ctrl+e to run the macro for ISERROR formula


If you want to undo please use Ctrl+z


Please note this will work for only formulas where cell reference is done and not for the below:


#DIV/0! Text
50 Not a cell reference formula
#VALUE! comments

Below is the macro code attached in the file:
Type SaveRange
Value As Variant
Address As String
End Type


Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange




Sub If_Is_Error()
'
' If_Is_Error Macro
' Changes =x to =if(iserror(x),"",x)
'
' Keyboard Shortcut: Ctrl+e
'
Dim ManualCalc As Boolean


If Application.Calculation = xlCalculationManual Then ManualCalc = True
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Dim x As String


' Save current values for later undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).Address = cell.Address
OldSelection(i).Value = cell.Formula
Next cell


' make each cell is_error="" if it is a formula
For i = 1 To UBound(OldSelection)
If Len(Range(OldSelection(i).Address).Formula) = 0 Then GoTo skip
If Left(Range(OldSelection(i).Address).Formula, 1) <> "=" Then GoTo skip
x = Right(Range(OldSelection(i).Address).Formula, Len(Range(OldSelection(i).Address).Formula) - 1)
x = "=IF(ISERROR(" & x & "),0," & x & ")"
Range(OldSelection(i).Address).Formula = x
x = 0
skip:
Next i


'Return claculation to automatic if required
If ManualCalc = True Then Application.Calculation = xlCalculationManual Else Application.Calculation = xlCalculationAutomatic
' If ManualCalc = True Then MsgBox "Calculation left on manual." & Chr(10) & "Press F9 to calculate sheet"


Application.ScreenUpdating = True


' Specify Undo
Application.OnUndo "Undo the IsError macro", "UndoIsError"


ManualCalc = 0


End Sub


Sub UndoIsError()


On Error GoTo Problem


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True


' Make sure the correct workbook and sheet are active
OldWorkbook.Activate
OldSheet.Activate


' Restore saved formulae
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Address).Formula = OldSelection(i).Value
Next i
Exit Sub


Problem:
MsgBox "Can't undo"
End Sub


Link for download:
http://www.esnips.com/doc/01ebaaa2-2b7d-4300-9fdc-ad01f06bb864/ISERROR-macro