CODEWITHSUNDEEP
vba
Michael
Michael

Reputation: 4461

Failed to check if range is nothing

Excel 2007

I'm stuck.

Private Sub check_status_correctness(a_row)

    Dim status_col_rng As Range
    Dim status_val As String
    Dim found_status As Range

    Set status_col_rng = Sheets("Settings").Columns(2)
    status_val = Sheets(MAIN_SHEET).Cells(a_row, STATUS_COL)
    Set found_status = status_col_rng.Find(status_val, LookIn:=xlValues, LookAt:=xlWhole)

    If found_status Is Nothing Then
        Call announce_error(a_row, STATUS_COL)
    End If
End Sub

found_status is Nothing at the breakpoing. If I scrutinize found_status in the debugger, it seems to be Nothing. But at the very moment found_status is Nothing gives me False.

Could you help me understand how to catch Nothing?

enter image description here

Upvotes: 0

Views: 48

Answers (1)

FunThomas
FunThomas

Reputation: 29592

Your check for Nothing is correct. As the image shows, the object variable found_status is not nothing (or with other words, was assigned to a Range)

Dim r As Range  ' r is not assigned, that means it is nothing
If r Is Nothing Then
    Debug.Print "r is Nothing"
End If

To check if a object variable is assigned:

Set r = ActiveSheet.Range("A1")
If Not r Is Nothing Then
    Debug.Print "r is assigned"
End If

Upvotes: 1

Related Questions