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!!!!!!
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!!!!!!
- 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.
- 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