Thread: Trying to find a formula that QUICKLY counts errors, formula or results, on a SS

I am trying to just count errors in a defind range on my spreadsheet. I tried using an array formula but it killed the processing time. I then tried a UDF, shown below, but it also kills the processing time because it runs more than once when doing a standard calculation. Is there a way to set the formula to only run at the end of a calc cycle?

Code:

Function CountErrors()
Dim rng, c As Range, e As Integer

Set rng = ActiveSheet.UsedRange
    For Each c In rng
        If InStr(1, c.Formula, "#REF!") Or IsError(c) Then
            CountErrors = CountErrors + 1
        End If
    Next c
End Function

Thanks!

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Hi,

A UDF will slow you down a touch. How about

=SUMPRODUCT((ISERROR(range))*1)

Thoughts?

I think you ought to read my last post.

Your title was fine. What I asked is that you add CODE tags.

Third time's the charm.

Try this; code goes in the Sheet module:

Code:

Private Sub Worksheet_Calculate()
    Dim nErr As Long
    
    On Error Resume Next
    nErr = Me.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Count
    
    Application.EnableEvents = False
    Range("A1").Value = "Errors: " & nErr   ' change range as desired
    Application.EnableEvents = True
End Sub

Did you read the part about marking threads as Solved?