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
 

Webbers

New Member
Joined
Oct 20, 2004
Messages
41
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.
@Fluff ,
That makes perfect sense. I appreciate you taking the time to explain. This code will be in my Personal workbook too... with the addition of commented notes for reference of what you just told me. I am so grateful for your time, help and the additional explanation!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@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!
You are welcome - thanks for the reply.
 

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