VBA to add IFERROR to all selected cells

esbencito · Jun 23rd 2017

Hi all,

I'm trying to add IFERROR to all cells within a selection, but only if the cell does not already have IFERROR. I have a VBA code that does that, but unfortunately it takes hours to run as it loops through 1.5 million cells. Also, I always have to change the range within the Macro, but I would prefer a more 'flexible' method where it checks all cells within a current selection on a certain sheet. Can anyone help me simplify/speed up the code? Also, I'm using Excel 2016 and noticed that my codes slowed down even more when using below code compared to Excel 2010 (1~ hour to now 3~ hours roughly).

Code

Sub IFERRORTOALLCELLS()
    'Optimize Code
    Call OptimizeCode_Begin
    Dim myRange As Range
    Dim cell As Range
    
'   Set range to apply this to
    Set myRange = Range("A1:FQ432")




'   Loop through range
    For Each cell In myRange
'       Check to see if formula (and not already if error)
        If Left(cell.Formula, 1) = "=" And Left(cell.Formula, 8) <> "=IFERROR" Then
            cell.Formula = "=IFERROR(" & Mid(cell.Formula, 2, Len(cell.Formula) - 1) & ",0)"
        End If
    Next cell
    'Optimize Code
    Call OptimizeCode_End
End Sub

Display More

esbencito · Jun 23rd 2017

Re: VBA to add IFERROR to all selected cells

Below is the code I use to speed up all my Macros. Does anyone know why it is significantly slower in Excel 2016?

Code

Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean




Sub OptimizeCode_Begin()




Application.ScreenUpdating = False




EventState = Application.EnableEvents
Application.EnableEvents = False




CalcState = Application.Calculation
Application.Calculation = xlCalculationAutomatic




PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False




End Sub
Sub OptimizeCode_End()




ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True




End Sub

Display More

**KjBox** · Jun 23rd 2017

Re: VBA to add IFERROR to all selected cells

The first thing I noticed was an error in your OptimizeCode_Begin procedure

Code

Application.Calculation = xlCalculationAutomatic

should be

Code

Application.Calculation = xlCalculationManual

If you set calulation to automatic that every formula is going to refresh for every iteration of your loop in the IFERRTOALLCELLS procedure, that will have a tremendous affect on code run time.

You can further speed up the code by using an array based code rather than an object based code.

Try these

Code

Option Explicit
Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean


Sub IFERRORTOALLCELLS()
    Dim x, i As Long, ii As Long
    
    With Sheets("Sheet1") '// Change sheet name to suit
        OptimizeCode_Begin
         
'// Load cell formulas into array x
        x = .[a1:fq432].Formula
         
'// Loop through both dimensions of array x, if formula does not start with IFERROR then add that function
        For i = 1 To UBound(x, 1)
            For ii = 1 To UBound(x, 2)
                If x(i, ii) Like "=*" Then
                    If Mid(x(i, ii), 2, 7) <> "IFERROR" Then
                        x(i, ii) = "=IFERROR(" & Mid(x(i, ii), 2, Len(x(i, ii)) - 1) & ",0)"
                    End If
                End If
            Next
        Next
        
'// Post updated formulas in array x back to the sheet
        .[a1:fq432].Formula = x


        OptimizeCode_End
    End With
    
End Sub
  
Public Sub OptimizeCode_Begin()
    Application.ScreenUpdating = False
    EventState = Application.EnableEvents
    Application.EnableEvents = False
    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    PageBreakState = ActiveSheet.DisplayPageBreaks
    ActiveSheet.DisplayPageBreaks = False
End Sub


Public Sub OptimizeCode_End()
    ActiveSheet.DisplayPageBreaks = PageBreakState
    Application.Calculation = CalcState
    Application.EnableEvents = EventState
End Sub

Display More

Also, in your code, even though Screen Updating is turned off in the OptimizeCode_Begin procedure, it will automatically be turned back on again when that procedure finishes. You would need to turn it off in the IFERRORTOALLCELLS procedure immediately after calling OptimizeCode_Begin. Using an array based code the screen is only updated once, when the array is posted back to the sheet, so there is no need to turn screen updating off.

esbencito · Jun 26th 2017

Re: VBA to add IFERROR to all selected cells

Impressive!! Just tested it, it reduced the run time from 2 hours to 10 seconds! Thanks!!!

Is there a way to change the VBA code slightly, so that it only applies IFERROR to a current selection rather than having it to change within the Macro every time (changing sheet name and array etc.)? I had something similar in another code (see below) I used before, but don't know how to apply it to the above.

Code

Sub IFERRORTOGETPIVOT()
    'Optimize Code
    Call OptimizeCode_Begin
Dim R As Range, V As Variant, i As Long
For Each R In Selection.SpecialCells(xlCellTypeFormulas)
    If InStr(R.Formula, "GETPIVOTDATA") > 0 Then
        V = Split(R.Formula, "GETPIVOTDATA")
        For i = 1 To UBound(V)
            V(i) = "IFERROR(GETPIVOTDATA" & V(i)
            V(i) = Replace(V(i), ")", "),0)", 1, 1)
        Next i
        R.Formula = Join(V, "")
    End If
Next R
    'Optimize Code
    Call OptimizeCode_End
End Sub

Display More

**KjBox** · Jun 26th 2017

Re: VBA to add IFERROR to all selected cells

Try this

Code

Sub IFERRORToSelection()
    Dim x, i As Long, ii As Long
     
    With Selection
        OptimizeCode_Begin
         
         '// Load cell formulas into array x
        x = .Formula
         
         '// Loop through both dimensions of array x, if formula does not start with IFERROR then add that function
        For i = 1 To UBound(x, 1)
            For ii = 1 To UBound(x, 2)
                If x(i, ii) Like "=*" Then
                    If Mid(x(i, ii), 2, 7) <> "IFERROR" Then
                        x(i, ii) = "=IFERROR(" & Mid(x(i, ii), 2, Len(x(i, ii)) - 1) & ",0)"
                    End If
                End If
            Next
        Next
         
         '// Post updated formulas in array x back to the sheet
        .Formula = x
         
        OptimizeCode_End
    End With
     
End Sub

Display More

esbencito · Jun 26th 2017

Re: VBA to add IFERROR to all selected cells

Perfect!! Exactly what I wanted! Thanks so much!!

Any chance you can have a look at my other VBA problem again?

http://www.ozgrid.com/forum/showthread.php?t=204243

VBA to add IFERROR to all selected cells

Participate now!

Share

Users Viewing This Thread