Excel VBA

Today I found some useful code I wanted to share with our readers.

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 iferror
‘if there’s an iferror already, ignore the formula
‘process all sheets in wb

 

‘Handle Errors
On Error GoTo Errorhandler

 

‘Ask user to open a new workbook 1#
NewWB = MsgBox(“Please click YES to select a workbook to process (Other than Active WorkBook)”, vbYesNo, “Process New WorkBook?”)

 

If NewWB = vbYes Then
‘ Prompt user to open workbook to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
End If

 

‘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
Range(“A1”).Select
Set wsRange = ws.UsedRange ‘2#

 

Dim r As Range
For Each r In wsRange

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

I have edited the code a little bit
1) User has choice to run the macro on active workbook or to select a new workbook and run the macro.
2) Extended macro range to entire used range in worksheet than just Current Region starting from (A1)

Courtesy: Excel Class Training

Leave a Reply

Your email address will not be published. Required fields are marked *

20 + eighteen =

This site uses Akismet to reduce spam. Learn how your comment data is processed.