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

DanielDaly

New Member
Joined
Aug 1, 2016
Messages
6
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:

Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,529
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.
Code:
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
 
Last edited:

DanielDaly

New Member
Joined
Aug 1, 2016
Messages
6
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."
 

DanielDaly

New Member
Joined
Aug 1, 2016
Messages
6
Hi mumps,

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

Thanks again.

Regards,

Daniel Daly
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,529
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

New Member
Joined
Aug 1, 2016
Messages
6
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,529
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.
Code:
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
 
Last edited:

DanielDaly

New Member
Joined
Aug 1, 2016
Messages
6
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

New Member
Joined
Aug 1, 2016
Messages
6
mumps,

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

Code:
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
 

Forum statistics

Threads
1,082,323
Messages
5,364,590
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top