Object Variable error on an If statement when no value is required-CodePudding

I have an issue with the following code

TestCheck:

    Dim Comm as Range
    Dim TestComment as Range


    Application.EnableEvents = False

    Set TestComment = Intersect(Application.ActiveSheet.Range("I:I"), Target)
    On Error GoTo Cancellation

    If TestComment = "" Then

    Else
        For Each Comm In TestComment
            If Not VBA.IsEmpty(Comm.Value) Then
                Comm.Offset(0, 1).Value = Date
                Comm.Offset(0, 1).NumberFormat = "dd/mm/yyyy"

                Application.EnableEvents = True
            End If
        Next
    End If

It keeps bringing up the error

'Object variable or with block variable not set'

on the If statement

If TestComment = "" Then

I get that it equals nothings so it freaks out, but I don't mind that it equals nothing, in fact in that case id rather it did nothing if it equals nothing instead of throwing an error. On Error GoTo doesn't seem to work either.

CodePudding user response：

If Target is not in column I, the function Intersect will return Nothing, so TestComment is set to Nothing.
You cannot check the value of Nothing, you will need to check if it is set to something, you can use is Nothing for that.

I assume your code is part of a event routine. Note that you shouldn't set EnableEvents to True while the code is still working - put it to the end of your code (behind label Cancellation) to ensure that it is executed even if an error occurs.

Application.EnableEvents = False
On Error GoTo Cancellation
Dim testComment As Range, comm As Range
Set testComment = Intersect(Range("I:I"), Target)

If Not testComment Is Nothing Then
    For Each comm In testComment
        If Not IsEmpty(comm.Value) Then
            comm.Offset(0, 1).Value = Date
            comm.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
        End If
    Next
End If

Cancellation: Application.EnableEvents = True