VBA Not Equal

Not Equal Operator in Excel VBA

VBA Not Equal is a logical function that returns either True or False. It is also called a negation operator.

Comparison operators are an essential part of data analysis and VBA programming also uses all kinds of comparison operators to automate tasks. Not Equal is one such comparison used to perform logical calculations, and it is important to know about the not equal operator in VBA. Usually, the logical test includes if the value is equal to, greater than, or less than. However, sometimes we need to do an inequality test to perform some logical tests.

An inequality test is not equal to a test where we apply a logical test and perform something if X is not equal to Y or else perform alternative action.

Meanwhile, the Not Equal To test is applied by using the symbols <> or ><

These symbols are a combination of comparison operators greater than (>) and less than (<). When we enter these two comparison operators in opposite directions, then, it becomes an inequality test.

How Not Equal To Operator Works in VBA?

Not Equal To operator works exactly opposite to the functionality of the equal to (=) operator. Equal To operator works for equality test where if the logical test is equal to the condition applied then it will return TRUE, if not it will return FALSE.

For example, if we apply the logical test 100 = 100, the equal operator returns TRUE because the applied logical test 100 = 100 is correct. However, if we replace the equal to sign with the not equal to sign, then we will get the FALSE result.

100 <> 100

The logical test 100 <> 100 is equal, and the inequality test (not equal to) finds it as a FALSE logical test and hence returns the result as FALSE.

In simple, in the logical test, one value should not be equal to the other to get the TRUE result with the Not Equal To test.

To test this practice, we have the following VBA code.

Part 1 – First, we have declared the variable “k” and assigned the data type Integer to it.

Part 2 – Next, we have assigned a value to the variable “k” as 10.

Part 3 – Then, in the message box we applied the inequality test of variable “k” which is not equal to the value 5.

Now, let’s run this code and see what result we get in return in the message box.

Clearly, we can see that the result of the inequality test is TRUE.

The value of variable “k” is 10 and we are testing if the variable “k” value is not equal to the number 5. The logical test 10 <> 5 is correct hence, we are getting the result as TRUE.

Similarly, now change the variable “k” value to 10 in the code.

From the previous code, we have changed only the value of variable “k” (marked in yellow).

Now, let’s run the code and see what result we get in return in the message box.

This time we got the result FALSE because the variable “k” value is 5 and the logical test value is also 5. Since both the values are equal inequality test fails, it returns the result as FALSE.

Examples

Example #1 – Apply Not Equal to Test Cell Values

VBA Not Equal To (< >) sign is used to test cell values and arrive at the result based on the values of two cells.

For example, look at the following data in Excel.

Here, we have two city names in cells A2 and B2 respectively. Now, let’s apply the Not Equal To test by using the following code.

Now, we have divided the code into 5 parts. Next, let us explain the code part by part for better understanding.

Part 1 – First, we have defined two variables City1 and City2 to two city values respectively.

Dim City1 As String
Dim City2 As String

Part 2 – Next, for variable City1 we are assigning the value of the cell A2 i.e., City name “Bangalore”. For variable City2 we are assigning the value of the cell B2 i.e., City name “Mumbai”.

City1 = Range(“A2”).Value
City2 = Range(“B2”).Value

Part 3 – Then, we have defined another variable to store the result of the Not Equal To test.

Dim NotEqualTest As Boolean

Part 4 – By using the variable defined in part 3 of the code we are applying the Not Equal To test for City1 and City2. Now, this will either return the result TRUE or FALSE to the variable.

NotEqualTest = City1 <> City2

Part 5 – Now, we are showing the value of the Not Equal To test variable.

MsgBox NotEqualTest

Next, let’s run the code and see what happens.

Clearly, we can see that the result is TRUE because City1 and City2 values are different hence both cities are not equal to each other. Hence, the Not Equal To test returns the result as TRUE.

Example #2 – Store Not Equal To Test in Cell

Testing cell values by applying the not equal to logical test is fine, but it is important to make use of the logical test operation and perform some action on the Excel worksheet.

Continuing with the previous example and the same data, we need to arrive at the result “City1 I Different Than City2” is Not Equal To test is TRUE or else we need to arrive at the result “City1 and City2 are same” in the status column.

Now, to arrive at results based on the logical test, we need to use the logical test formula IF condition. The following code uses the IF condition along with the Not Equal To test and arrive result in the status column.

Meanwhile, we can see that parts 1 and 2 remain the same compared to the previous code. By removing parts 3 to 5, we have added a new part 3 by using the IF logical condition.

Part 3 – Now, we have applied the IF logical condition City1 < > City2, if this Not Equal To test is TRUE then we need to insert the value in cell C2 as “City 1 is different than City2”.

If the Not Equal To test is FALSE, then we need to insert the value in cell C2 as “City1 is the same as City2”.

When we run this code, we will get the value in the status column cell C2 like the following.

We have got the result “City1 is different than City2” because the Not Equal To test is TRUE.

Example #3 – Loop Through Multiple Cells and Apply Not Equal to Operator

When we need to perform the same set of Not Equal To operations, we need to use loops to avoid lengthy codes. For example, look at the following data in Excel.

This is the data of people who bought lottery tickets along with the lottery ticket number and winning number in columns A, B, and C respectively. Now, in column D, we need to find who won the lottery ticket.

To find the winner, we need to apply the logical test. Lottery Number <> Winning Number. If the lottery number is equal to the winning number, then they are the winners. Meanwhile, if the lottery number is not equal to the winning number, then, they are the losers.

The following code will loop through all the cells and find the winner of the lottery ticket.

Sub Example3_Not_Equal_Test()
Dim k As Long
Dim LR As Long
‘Find the last used row
LR = Cells(Rows.Count, 1).End(xlUp).Row
For k = 2 To LR
If Cells(k, 2).Value <> Cells(k, 3).Value Then
Cells(k, 4).Value = “Oops!!! Better Luck Next Time!!!”
Else
Cells(k, 4).Value = “Congratulation on Winning the Lottery”
End If
Next k
End Sub

The above code will check the lottery number in column B (all the cells) against the winning lottery number in Column C (all the cells) and returns a value “Oops!!! Better Luck Next Time!!!” if the lottery number is not equal to the winning lottery number. Or else, it will return the result “Congratulations on Winning the Lottery”.

The winner of the lottery is “Tim” in cell A5 because the Not Equal To test failed because the lottery number and winning lottery number are the same. So, he is the winner.

Example #4 – Hide and Unhide Sheets

Hide Worksheet Except One – There might be a situation where we need to hide worksheets if the worksheet name is not equal to something. For example, the following code will hide all the worksheets if the worksheet name is not equal to “Sales”.

Sub Example4_Not_Equal_Test()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> “Sales” Then
Ws.Visible = xlSheetVeryHidden
End If
Next Ws
End Sub

The above code will hide all the worksheets except the sheet named “Sales”.

Unhide Worksheet Except One – We may need to do the vice versa of the above method. We may need to unhide all the worksheets except one worksheet.

The following code will unhide all the worksheets except the worksheet named “Sheet2”.

Sub Example5_Not_Equal_Test()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> “Sheet2” Then
Ws.Visible = xlSheetVisible
End If
Next Ws
End Sub

When some of the worksheets are hidden, then, we can use the above code to unhide all of them except Sheet2.

Likewise, we can change the sheet name after the Not Equal To sign in the logical test.

Important Things to Note

• When we use the greater than (>) and less than (<) signs in opposite directions, it becomes a Not Equal To comparison symbol.
• Remember, we need to enter the not equal to sign without entering any space character in between.
• Also, the Not Equal To comparison operator always returns the Boolean value as result i.e., TRUE or FALSE.
• Meanwhile, TRUE is treated as 1, and FALSE is treated as 0 in Boolean values.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Not Equal, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Not Equal. Here we explain how Not Equal to operator works in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA Operators
• VBA FileSystemObject
• VBA Case Statement