Need Updates to Error List Macro

Webbers

New Member
Joined
Oct 20, 2004
Messages
41
Hi all!

I found a code, and it works perfectly, as it has, but I now need to make some modifications, and I am having difficulty doing so. Thank you in advance, your time and help are greatly appreciated!!!!!!
  1. I do not want a NEW sheet added to the beginning of the workbook. I have a sheet called "Errors" (which has a code name of Sheet2). I would prefer to use the code name if possible, as users often change the sheet names... which I would have no control of.
  2. I would like the formula to display in column D on the Errors tab. This is very helpful in my development process and when I am training others. The visualization of the errors.


VBA Code:
Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
  
  Sheets.Add Before:=Sheets(1)
  nr = 1
  For i = 2 To Sheets.Count
    Set rErrors = Nothing
    On Error Resume Next
    Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
    On Error GoTo 0
    If Not rErrors Is Nothing Then
      sName = Sheets(i).Name
      For Each r In rErrors
        nr = nr + 1
        With Sheets(1)
          .Cells(nr, 1).Value = sName
          .Cells(nr, 2).Value = r.Address(0, 0)
          .Cells(nr, 3).Value = r.Text
        End With
      Next r
    End If
  Next i
  Sheets(1).Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
 
  nr = 1
  For i = 2 To Sheets.Count
    Set rErrors = Nothing
    On Error Resume Next
    Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
    On Error GoTo 0
    If Not rErrors Is Nothing Then
      sName = Sheets(i).Name
      For Each r In rErrors
        nr = nr + 1
        With Sheet2
          .Cells(nr, 1).Value = sName
          .Cells(nr, 2).Value = r.Address(0, 0)
          .Cells(nr, 3).Value = r.Text
          .Cells(nr, 4).Value = Replace(r.Formula, "=", "")
        End With
      Next r
    End If
  Next i
  Sheet2.Range("A1:D1").Value = Array("Sheet", "Cell", "Error", "Formula")
End Sub
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If the sheet with code name Sheet2 is where you want to list all errors from other sheets:
VBA Code:
Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
  
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        Set rErrors = Nothing
        On Error Resume Next
        Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rErrors Is Nothing Then
          sName = Sheets(i).Name
          For Each r In rErrors
            nr = nr + 1
            With Sheet2
              .Cells(nr, 1).Value = sName
              .Cells(nr, 2).Value = r.Address(0, 0)
              .Cells(nr, 3).Value = r.Text
            End With
          Next r
        End If
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
Oops did it for sheet1 & not sheet2.
I've corrected my code in post#2
 

Webbers

New Member
Joined
Oct 20, 2004
Messages
41

ADVERTISEMENT

Sub List_Errors() Dim rErrors As Range, r As Range Dim i As Long, nr As Long Dim sName As String nr = 1 For i = 2 To Sheets.Count Set rErrors = Nothing On Error Resume Next Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 If Not rErrors Is Nothing Then sName = Sheets(i).Name For Each r In rErrors nr = nr + 1 With Sheet2 .Cells(nr, 1).Value = sName .Cells(nr, 2).Value = r.Address(0, 0) .Cells(nr, 3).Value = r.Text .Cells(nr, 4).Value = Replace(r.Formula, "=", "") End With Next r End If Next i Sheet2.Range("A1:D1").Value = Array("Sheet", "Cell", "Error", "Formula") End Sub
Hello @Fluff , thanks for your quick reply. I tried your code, but received this error. And when I selected Debug, it referenced this part of the code.
 

Attachments

  • 2020-10-28_13-56-59.png
    2020-10-28_13-56-59.png
    3.7 KB · Views: 3
  • 2020-10-28_13-59-02.png
    2020-10-28_13-59-02.png
    18.4 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
Is the code in the same workbook as the Errors sheet?
 

Webbers

New Member
Joined
Oct 20, 2004
Messages
41

ADVERTISEMENT

If the sheet with code name Sheet2 is where you want to list all errors from other sheets:
VBA Code:
Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        Set rErrors = Nothing
        On Error Resume Next
        Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rErrors Is Nothing Then
          sName = Sheets(i).Name
          For Each r In rErrors
            nr = nr + 1
            With Sheet2
              .Cells(nr, 1).Value = sName
              .Cells(nr, 2).Value = r.Address(0, 0)
              .Cells(nr, 3).Value = r.Text
            End With
          Next r
        End If
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub
@JoeMo,

Thanks for the quick response! Your code also came back with the same error as I got with Fluff's. I did add/edit 2 lines of your code as I wanted the formulas to display in column D. That information was on Fluff's code, so I just copied it over to yours. But both broke in the same location, I do not understand. My workbook has 8 tabs, including "Errors", and all tabs (except) Errors should be reviewed for any potential errors.
 

Attachments

  • 2020-10-28_14-14-28.png
    2020-10-28_14-14-28.png
    3.8 KB · Views: 1
  • 2020-10-28_14-13-46.png
    2020-10-28_14-13-46.png
    22.4 KB · Views: 1

Webbers

New Member
Joined
Oct 20, 2004
Messages
41
Is the code in the same workbook as the Errors sheet?
@Fluff,

I initially had it in my Personal;. workbook... as that is where the original code was, which I had made a copy of. I often run code directly from my Personal workbook, so why did it not work in this case?
 

Attachments

  • 2020-10-28_14-21-48.png
    2020-10-28_14-21-48.png
    14 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
You can only refer directly to a sheet codename if it's in the same workbook as the code.
Your basic options are
1) keep the code in the workbook it needs to be used on.
2) use the sheet name & not the code name
3) loop through the sheets to find the correct codename & assign that to a variable.

Also when using codenames I would advise changing them, rather than using the defaults. Sheet codenames work in the same way as sheet names, in that if you delete Sheet1 & then later on add a new sheet that will become Sheet1.
 

Webbers

New Member
Joined
Oct 20, 2004
Messages
41
If the sheet with code name Sheet2 is where you want to list all errors from other sheets:
VBA Code:
Sub List_Errors()
  Dim rErrors As Range, r As Range
  Dim i As Long, nr As Long
  Dim sName As String
 
  nr = 1
  For i = 1 To Sheets.Count
    If Sheets(i).CodeName <> "Sheet2" Then
        Set rErrors = Nothing
        On Error Resume Next
        Set rErrors = Sheets(i).UsedRange.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rErrors Is Nothing Then
          sName = Sheets(i).Name
          For Each r In rErrors
            nr = nr + 1
            With Sheet2
              .Cells(nr, 1).Value = sName
              .Cells(nr, 2).Value = r.Address(0, 0)
              .Cells(nr, 3).Value = r.Text
            End With
          Next r
        End If
    End If
  Next i
  Sheet2.Range("A1:C1").Value = Array("Sheet", "Cell", "Error")
End Sub
@JoeMo ,

At Fluff's suggestion (for his code), I moved the code to the workbook I was working in (rather than personal), and your code worked perfectly. Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top