Geek asks and answers

Is the most effective method for checking cell batches an error?

I have a spreadsheet that contains many function calls to query data. I am writing a function (in VBA) to check if any of the cells contains the error value "#VALUE", etc.

At the moment, I repeat line by line, column by column, and first check if the cell contains a formula, then, if so, checking instr for "#VALUE", "# N / A", etc.

However, I was wondering if this would be a faster simulation of clicking on an entire column in excel, and then "ctrl + f" for the value ... in VBA.

What would be the most efficient way? I am checking a sheet of 27 columns x 1200 row size.

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

EDIT2 I really need to write a macro that returns resutls, just like "find everything." I used find, and I can get a boolean, but find all does not write VBA code ....

+5
source share
2 answers

You can use SpecialCellsto 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
+5
source

Given that you need the most efficient method, you can try this approach, which avoids the slow range cycle

  • SpecialCells chichi ( )
  • Find , (1)

R1C1 Find, Application ( )

, , . R1C1 , .

, A1

  • = SUM (A1: A4) A5 SUM(B1:B4) in B5`
  • R1C1 =SUM(R[-4]C:R[-1]C)

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
+1

More articles:


All Articles