Efficient formula or code to determine number of errors in range VBA

user_14326550 0
Viewed 725

I am attempting to retrieve the number (count) of "#N/A" and "#Value" cells within a range (EG A1:A100).

So far I have 2 solutions:

  1. Array formula : "=COUNT(IF(ISERROR(A1:A100), 1, 0))" cntr + shft + entr

the solution works but lags a dynamic element. If it does update, it is slow and late to the party.

  1. Create a custom formula (nested in a module)
Public Function ErrorArray(Rng As Range)
Dim ErrorCount As Integer
Dim Cell As Range
Application.Volatile
For Each Cell in Rng
      If Cell.Errors.Items(xlEvaluateToError) = True Then 
          ErrorCount  = ErrorCount + 1
      End If
Next Cell
ErrorArray = ErrorCount
End Function

*Please excuse any errors, it did work so thats not the point.

The issue with this solution is the massive drop in workbook performance. Does anyone else know an efficient and dynamic solution, either formula or code?

excel-formula vba excel
4 Answers

You could use:

=SUM(COUNTIF(A1:A100,{"#N/A","#VALUE!"}))
user_36119890

Also you can use for any error:

=SUM(--ISERROR(A1:A100))

Enter as array formula

enter image description here

user_121888610

NOTE: *The following function will work if called from a vba Macro (Sub). The .SpecialCells method doesn't seem to work when called from a worksheet via a UDF`

Public Function ErrorArray(Rng As Range) As Long
    Dim c As Range
        Set c = Rng.SpecialCells(xlCellTypeFormulas, xlErrors)
        If Not c Is Nothing Then
            ErrorArray = c.Count
        Else
            ErrorArray = 0
        End If
End Function

If you just want to count only the #N/A and #VALUE! errors:

Public Function ErrorArray(Rng As Range) As Long
    Dim r As Range, c As Range
    Dim errCount As Long
        Set r = Rng.SpecialCells(xlCellTypeFormulas, xlErrors)
        If Not r Is Nothing Then
            For Each c In r
                'Debug.Print c.Text, c
                Select Case c
                    Case CVErr(2042), CVErr(2015)
                        errCount = errCount + 1
                End Select
            Next c
        Else
            errCount = 0
        End If
 ErrorArray = errCount

End Function

If you have very large numbers of errors, the above code can be sped up by using variant arrays. Let me know.

user_28729220

Error Cells Count

  • Copy the code into a standard module, e.g. Module1.
  • In Excel use it like this: =ErrorCellsCount(A1:A100)

The Code

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Counts the number of cells containing an error value.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function ErrorCellsCount(CheckRange As Range) As Long
    If Not CheckRange Is Nothing Then
        If CheckRange.Rows.Count > 1 Or CheckRange.Columns.Count > 1 Then
            ' Check Range contains multiple cells.
            Dim Data As Variant
            Data = CheckRange.Value
            Dim Element As Variant
            For Each Element In Data
                If IsError(Element) Then
                    ErrorCellsCount = ErrorCellsCount + 1
                End If
            Next Element
        Else
            ' Check Range contains one cell only.
            If IsError(CheckRange) Then
                ErrorCellsCount = 1
            End If
        End If
    End If
End Function
user_98140690
Related