VBA To Add IfError Functions To Every Formula On Every Sheet

VBA To Add IfError Functions To Every Formula On Every Sheet

Today we found some useful code here we wanted to share with our readers

vba class blog - iferror… a macro to automatically add an IfError function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used.

Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close the file without saving changes. The macro leaves a selected workbook open so you can review the changes before deciding to save or not.

To use this code, press the Alt+F11 key to open the VB Editor.
Select the Insert menu > Module command.
Then paste the following code into the empty window:
To run the program, press the F5 key, or from Excel select the Ribbon View Tab > Macros button and run the AddIfError macro.

Public Sub AddIffError()
‘purpose: IF there is a formula add ifferror
‘if there’s an iferror already, ignore it
‘process all sheets in wb

‘Handle Errors
‘On Error GoTo Errorhandler

‘Step 10: Ask user to open a wb
MsgBox “Please click OK to select a workbook to process”

‘Step20: Prompt user to open wb to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

‘Step30: On every sheet add iferror
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

If ws.Visible = True Then ws.Select

‘select all cells
If Len(ActiveWorkbook.Name) – InStr(1, ActiveWorkbook.Name, “.xls”) = 3 Then
Range(“A1:IV65535”).Select
Else
Range(“A1:XFD1048576”).Select
End If

Dim r As Range
For Each r In Selection
If Left(r.Formula, 1) = “=” Then
If Left(r.Formula, 8) = “=IFERROR” Then
‘Do Nothing
Else
Dim myformula
myformula = Right(r.Formula, Len(r.Formula) – 1)
r = “=IfError(” & myformula & “,0)”
End If
End If
Next r

Next ws

Exit Sub
Errorhandler:
If Err.Number <> 0 Then
MsgBox “Something went wrong”
End If

End Sub

To learn more about VBA programming check out our 3-day Excel VBA course in New York City.

View the original blog post here: http://excelclasstraining.com/excel-training-blog/excel-class-vba-macro-adds-iferror-functions-to-every-formula/