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