CODEWITHSUNDEEP
vbaexcelerror-handlingdependenciesruntime-error
Ethun_Hunt
Ethun_Hunt

Reputation: 267

Handling error for "cell.dependents.count" for cells having no dependents in excel

I am trying to write macro to automatically categorize cells from selected range into "input", "output" and "calculation" cells. I am getting "Run-time error '1004': No cells were found" for the statements containing rCell.Precedents.Count or rCell.Dependents.Count when rcell is not having precedent or dependent. I tried

IsEmpty(rCell.Dependents)

But still I am getting same error.

Private Sub test_v05()
    ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone
    Dim Target As Range, rCell As Range
    Set Target = Sheets("Sheet1").Range("A1:C10")
    For Each rCell In Target.Cells
        If IsEmpty(rCell.Value) = True Then GoTo AllDone:

        If rCell.Precedents.Count = 0 And rCell.Dependents.Count > 0 Then
        rCell.Style = "Input"
        GoTo AllDone:
        End If

        If rCell.HasFormula And rCell.Dependents.Count = 0 Then
        rCell.Style = "Output"
        GoTo AllDone:
        End If

        If rCell.Precedents.Count > 0 And rCell.Dependents.Count > 0 Then
        rCell.Style = "Calculation"
        End If
AllDone:
    Next rCell
End Sub

What is best way to handle error while tracing dependents or precedents of cells which don't have one?

Upvotes: 2

Views: 1220

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub Dependental()
    Dim n As Long
    n = 0

    On Error Resume Next
        n = Cells.Precedents.Count
    On Error GoTo 0

    MsgBox "there are " & n & " cells with dependents"
End Sub

Upvotes: 1

Related Questions