CODEWITHSUNDEEP
excelvba
J01
J01

Reputation: 145

Return true if any value in an array contains error value

Just need to set a boolean to true if any value in an array (array = ActiveSheet.Range("A1").CurrentRegion.Value) contains #NA error.

I've read that wks.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) can do this but I'm not sure how to apply it to my code. Any other methods would also be okay.

Upvotes: 0

Views: 466

Answers (2)

MGP
MGP

Reputation: 2551

You could use the following code:

It's basically looking through all the values in the cells:

Sub check_for_errors()
    Dim ws As Worksheet
    Dim arr As Variant
    Dim i As Long, j As Long
    Dim errorFound As Boolean
    
    Set ws = ActiveSheet
    
    arr = ws.Range("a1").CurrentRegion.Value
    errorFound = False
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If IsError(arr(i, j)) Then
                errorFound = True
                Exit For
            End If
        Next j
        If errorFound Then Exit For
    Next i
    
    Debug.Print errorFound
End Sub

Upvotes: 2

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub checkNAError()
   Dim wks As Worksheet, rngErr As Range, celV As Range
   
    Set wks = ActiveSheet 'use here the sheet you need to check
    On Error Resume Next
      Set rngErr = wks.cells.SpecialCells(xlCellTypeFormulas, xlErrors)
   On Error GoTo 0
   If Not rngErr Is Nothing Then
        For Each celV In rngErr.cells
            If celV.value = CVErr(2042) Then MsgBox "#N\A error on cell " & celV.Address
        Next
    Else
        MsgBox "No any error in sheet " & wks.name
   End If
End Sub

Upvotes: 2

Related Questions