2

I have code that searches a worksheet for errors and puts the cell address and the error on a separate sheet.

Set RErrors = Nothing
On Error Resume Next
Set RErrors = Sheets("Sheet1").ListObjects("Table").Range.SpecialCells(xlCellTypeFormulas, 16)
On Error GoTo 0
Counter = 0
If Not RErrors Is Nothing Then
    Workbooks(Different_Workbook).Sheets(Different_Sheet).Activate
    For Each x In RErrors
        ActiveSheet.Range("A1").Offset(Counter, 0).Formula = x.Address
        ActiveSheet.Range("B1").Offset(Counter, 0) = x
        x.ClearContents
        Counter = Counter + 1
    Next x
End If

The code is working but I am planning to fill all the error cell address and corresponding errors in a 2D array, and present a pivoted view for each type of error and the number of cells.

I am facing two problems--

  1. How do I initialize the array when I don't know the number of errors (and without Dim, I can't fill)? Perhaps it would mean to run a loop to first find all errors then get the sum of errors and then Dim and then again a loop to fill the error array, so two loops for one thing?

  2. How do I manipulate the error (and position) filled array to get a result which shows how many cells each type of error has (like a pivot)?

Community
  • 1
  • 1
Repairer
  • 99
  • 7

1 Answers1

3

For the initialization of array, which you do not know the number of items in, Redim is used. Redim MS Documentation.

However, reading the problem it seems that the goal is to make some kind of a summary of the numbers of the errors. Let's consider the following errors:

enter image description here

Which comes from the following code:

Sub PopulateErrors()

    Dim myCell As Range
    With Worksheets(1)
        For Each myCell In .Range("A1:A5")
            myCell.Formula = "=0 / 0"
        Next myCell

        For Each myCell In .Range("B1:B2")
            myCell.Formula = "=nonexistingformula()"
        Next myCell

        For Each myCell In .Range("C1:C3")
            myCell.Formula = "=4+""ky"""
        Next myCell
    End With

End Sub

These could be summarized with a dictionary object like this:

Sub ErrorDataSummary()

    Dim myCell As Range
    Dim myDict As Object

    Set myDict = CreateObject("Scripting.Dictionary")


    For Each myCell In Worksheets(1).Range("A1:C5")
        If IsError(myCell) Then
            If myDict.exists(myCell.Text) Then
                myDict(myCell.Text) = myDict(myCell.Text) + 1
            Else
                myDict(myCell.Text) = 1
            End If
        End If
    Next myCell

    Dim myKey As Variant
    For Each myKey In myDict.keys
        Debug.Print myKey; myDict(myKey)
    Next

End Sub

Running the code, the following summary is obtained:

enter image description here

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thanks Vityata for showing the solution with a dictionary...have not yet tried it with my code but perhaps it will surely work, just a question, with ReDim also the filling of the array has to fall after we have Redimmed, I mean first Dim, then a loop to count all errors, then ReDim and then a loop to fill array. The ReDim has to come before filling the array. So ReDim won't help. Please correct me if wrong. Of course, none of this will be needed with the approach you showed. – Repairer Dec 25 '19 at 12:36
  • 1
    @Repairer - you are welcome. Considering redim, it should be in a loop (but this is in general non-optimal). https://stackoverflow.com/questions/39827849/redim-preserve-in-for-loop – Vityata Dec 25 '19 at 12:50
  • 2
    Another suggestion is to go through specialcells and avoid a loop over cells that might not hold an error. Nice answer though – JvdV Dec 25 '19 at 13:07