Need VBA macro code to apply to all or select Worksheets at once

[DanielDaly]

Hi Everybody,

Its killing me that I cannot find on the internet how to make a simple VBA coded macro apply to all or select sheets in a workbook. The following code works and I just want to be able to apply it to all or select worksheets:

Sub Add_IFERROR_Activesheet()
Dim myCell As Range
    Application.ScreenUpdating = False
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
Application.ScreenUpdating = True
End Sub

Any suggestions or solutions would be really appreciated. Thank you.

Best regards,

Daniel Daly

 

[mumps]

This macro will apply to all the sheets. If you want it to apply to selected sheets, then we would have to know the names of those sheets or the names of the sheets to exclude.

Sub Add_IFERROR_Activesheet()
    Dim myCell As Range
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Cells.SpecialCells(xlCellTypeFormulas, 23).Select
        For Each myCell In Selection.Cells
            If myCell.HasFormula And Not myCell.HasArray Then
                myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
            End If
        Next myCell
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[DanielDaly]

Thank you for the response, Mumps, but unfortunately it does not seem to work.

Im getting the message box, "Run-time error '1004':

No cells were found."

 

[mumps]

When you click 'Debug' which line is highlighted?

 

[DanielDaly]

Hi mumps,

The 6th line of the code is highlighted, "ws.Cells.SpecialCells(xlCellTypeFormulas, 23).Select"

Thanks again.

Regards,

Daniel Daly

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[DanielDaly]

Hi mumps,

I am just trying to apply the IFERROR formula to all the cells with formulas in the workbook (or select worksheets) that have errors (#N/A, #DIV/0, #VALUE, etc.) all at once, instead of having to go sheet by sheet running the macro.


Here is the spreadsheet:
https://www.dropbox.com/s/00jx5agw2es0944/081%20NAAIS%202017%20BP%20Draft.xls?dl=0

Here is the macro:
https://www.dropbox.com/s/wayx8lixx8f7z7z/Module1%20-%20IFERROR%20Wrap.bas?dl=0


I really appreciate all your help.

Best regards,

Daniel Daly

 

[mumps]

Looking at your file, I noticed that some sheets are protected. In order for the macro to work, the code must first unprotect the sheet, apply the IFERROR formula and then protect it again. This macro should do that. In the code, replace "mypassword" with your actual password (2 occurrences). If there are some sheets to which you don't want to apply the IFERROR formula, then the code has to be modified to reflect that. This macro loops through all the sheets, detects if the sheet is protected and if it is, it unprotects it. I tried this macro on one unprotected sheet and it worked properly although it was very slow. There may be a faster way to apply the IFERROR formula but I'm not sure on how to do that. You may want to post the code in a new thread and ask for some way to speed it up.

Sub Add_IFERROR_Activesheet()
    Dim myCell As Range
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Sheets
        If ws.ProtectContents = True Then
            ws.Unprotect Password:="mypassword"
            For Each myCell In ws.Cells.SpecialCells(xlCellTypeFormulas, 23).Cells
                If Not myCell.HasArray Then
                    myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
                End If
            Next myCell
            ws.Protect Password:="mypassword"
        Else
            For Each myCell In ws.Cells.SpecialCells(xlCellTypeFormulas, 23).Cells
                If Not myCell.HasArray Then
                    myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
                End If
            Next myCell
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[DanielDaly]

Thanks again, mumps. Yeah, I think its working but like you said its very slow. I'll repost asking for a speed up mechanism. Thanks!

Regards,

Daniel Daly

 

[DanielDaly]

mumps,

If you insert the following code, it will make any VBA macro fly:

Sub Macro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Activesheet.DisplayPageBreaks = False
 'Place your macro code here
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Activesheet.DisplayPageBreaks = True
End Sub