VBA: How to Use IF NOT (With Examples)


You can use the following basic syntax to use IF NOT logic in VBA to test if some condition is not met:

Sub IfNot()
    Dim i As Integer
    
    For i = 2 To 11
        If Not Range("B" & i) = "West" Then
        Result = "Not West"
        Else
        Result = "West"
        End If
    Range("C" & i) = Result
    Next i

End Sub

This particular example checks if each cell in the range B2:B12 is not equal to “West” and then assigns either “Not West” or “West” to each corresponding cell in the range C2:C12.

The following example shows how to use this syntax in practice.

Example: How to Use IF NOT in VBA

Suppose we have the following dataset in Excel that shows the team name and division of various basketball teams:

Suppose we would like to assign a value of “West” or “Not West” to each cell in column C based on whether or not each team belongs to the West division or not.

We can create the following macro to do so:

Sub IfNot()
    Dim i As Integer
    
    For i = 2 To 11
        If Not Range("B" & i) = "West" Then
        Result = "Not West"
        Else
        Result = "West"
        End If
    Range("C" & i) = Result
    Next i

End Sub

When we run this macro, we receive the following output:

The values in column C tell us whether or not each division in column B is equal to “West” or not.

For example:

  • Team A belongs to the “West” so column C displays “West”
  • Team B belongs to the “East ” so column C displays “Not West”
  • Team C belongs to the “East ” so column C displays “Not West”
  • Team D belongs to the “North” so column C displays “Not West”

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Use IF OR to Test Multiple Conditions
VBA: How to Use IF AND to Test Multiple Conditions
VBA: How to Check if String Contains Another String

Leave a Reply

Your email address will not be published. Required fields are marked *