CODEWITHSUNDEEP
excelvba
Raveel
Raveel

Reputation: 9

What does Not and Is Nothing do in this code?

The below code sees if there is a value in "A2" cell, then it clears the values in "C1".

Can someone explain me what does Not and Is Nothing do is the following code?

I need to change this code to work vice Versa, which I can only do if I can understand the code properly.

If some one can help me that would be good.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("C1").ClearContents
    End If
End Sub

I have tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("C1").ClearContents
    Else
         If Not Intersect(Target, Range("C1")) Is Nothing Then
             Range("A2").ClearContents
         End If
    End If
End Sub

Upvotes: 0

Views: 1101

Answers (1)

Dominique
Dominique

Reputation: 17493

Let's analyse this piece of code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("C1").ClearContents
    End If
End Sub

This is an event, which is launched when something changes in a worksheet. The place where the changes appear is the so-called Target.

That Target can contain just one cell, or multiple cells (it's possible to modify different cells, it's also possible that cells contain a formula, whose result changes, together with some other changes, ...).

We would like to know if there are any changes in the cell "A2", how to do that?
Simple: we take the intersection of the Target and cell "A2". Two possibilities: either it contains something or not. If it contains something, then you can say that that intersection is not nothing.

So, by checking if the intersection of the Target and cell "A2" is not nothing, you can check if anything has been changed in cell "A2".

Upvotes: 1

Related Questions