Errors in Excel are different – from “Division by Zero” (#Div/0) and #Name to #REF! and #Num. There are lots of ways to summarize these into some kind of report and in the current article I will show the VBA way how to do it.
First, in order to have the same results, we should be getting the same errors. The code below generates 12 errors on every worksheet of the workbook, with some offset, thus the errors are on different cells:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub PopulateErrors() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets With wks Dim myCell As Range For Each myCell In wks.Range("A1:A5").Offset(wks.Index) myCell.Formula = "=5 / 0" Next myCell For Each myCell In .Range("B1:B6").Offset(wks.Index) myCell.Formula = "=formulathatdoesnotexist()" Next myCell For Each myCell In .Range("A2:C2").Offset(wks.Index) myCell.Formula = "=6+""vitosh""" Next myCell End With Next End Sub |
If you want to have a summary of the type of errors per worksheet, this is what is quite easily possible with VBA:
For the details, the picture in the immediate window is a bit different:
The VBA for the summary looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub ErrorDataSummary() Dim myCell As Range Dim myDict As Object Set myDict = CreateObject("Scripting.Dictionary") Dim wks As Worksheet Dim errorName As String For Each wks In ThisWorkbook.Worksheets For Each myCell In wks.UsedRange If IsError(myCell) Then errorName = myCell.Text & " in " & wks.Name If myDict.exists(errorName) Then myDict(errorName) = myDict(errorName) + 1 Else myDict(errorName) = 1 End If End If Next myCell Next Dim myKey As Variant For Each myKey In myDict.keys Debug.Print myKey; myDict(myKey) Next End Sub |
And the VBA for the details is this one:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Sub ErrorDataDetails() Dim myCell As Range Dim myDict As Object Set myDict = CreateObject("Scripting.Dictionary") Dim wks As Worksheet Dim errorName As String Dim errorAddress As String For Each wks In ThisWorkbook.Worksheets For Each myCell In wks.UsedRange If IsError(myCell) Then errorName = myCell.Text & " in " & wks.Name errorAddress = myCell.Address Debug.Print errorName & " -> " & errorAddress End If Next myCell Next End Sub |
Cheers! 🙂