Most efficient technique to check lots of cells contain an error?

Question

I have a spreadsheet which contains lots of function calls to request data. I am writing a function (in VBA) to check whether any of the cells contains an error value "#VALUE" etc.

At the moment I am iterating row by row, column by column and first checking if the cell contains a formula, then if it does, checking instr for "#VALUE", "#N/A" etc.

However, I was wondering whether it would be quicker simulating clicking a whole column in excel and then "ctrl + f" for a value... in VBA.

What would be the most efficient way? I am checking a sheet 27 columns x 1200 rows large.

EDIT Ive just realised there are some cells which have "#N/A" and this is because they do not contain a particular formula. I need to only search in cells which contain a particular formula.... is this possible?

EDIT2 I effectively need to record a macro which returns the resutls, exactly like "find all". I have used "find" and i can get a boolean, but "find all" doesnt record any VBA code....

Answer 1

You can use SpecialCells to return only cells containing errors.

Sub Demo()
    Dim sh As Worksheet
    Dim rng As Range, cl As Range

    For Each sh In ActiveWorkbook.Worksheets
        Set rng = Nothing
        On Error Resume Next
        Set rng = sh.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
        If rng Is Nothing Then
            Debug.Print "No Errors"
        Else
            For Each cl In rng
                If cl.Formula Like "*" Then  ' <-- replace * with your criteria
                    Debug.Print cl.Address
                End If
            Next
        End If
    Next
End Sub

Answer 2

Given you wanted the most efficient method you could try this approach which avoids a slow range loop

1. Loops through SpecialCells formulae chichi contain errors (as per the other solution)
2. Uses Find to detect specific formulae rather than a simple loop through every cell in (1)

This code uses the R1C1 method to feed into the Find so the code changes this Application setting if necessary (and then back at the end)

I suggest you record the formula you wish to find to then enter this in. The big advantage of R1C1 notation is that it is agnostic of actual row and column location.

For example in A1 notation a formula of

• =SUM(A1:A4) in A5 would require a different search for SUM(B1:B4) inB5`
• in R1C1 this is =SUM(R[-4]C:R[-1]C) in both cases

code

Sub Demo()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim strAddress As String
    Dim bRefSTyle

    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
        bRefSTyle = True
    End If

    For Each ws In ActiveWorkbook.Worksheets
        Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
        If rng1 Is Nothing Then
            Debug.Print ws.Name & ": No Formulae errors"
        Else
            'search errors for particular formula
            'this sample looks for a formula which SUMS the four cells directly above it
            Set rng2 = rng1.Find("=SUM(R[-4]C:R[-1]C)", , xlFormulas, xlWhole)
            If Not rng2 Is Nothing Then
                strAddress = rng2.Address
                Set rng3 = rng2
                Do
                 Set rng2 = rng1.Find("=SUM(R[-4]C:R[-1]C)", rng2, xlFormulas, xlWhole)
                    Set rng3 = Union(rng2, rng3)
                Loop While strAddress <> rng2.Address
                Debug.Print ws.Name & ": " & rng3.Address
            Else
                Debug.Print ws.Name & ": error cells, but no formulae match"
            End If
        End If
    Next
    'restore styles if necessary
    If bRefSTyle Then Application.ReferenceStyle = xlA1
End Sub