Operator Eqv is Used to perform a logical equivalence on two expressions or statements. For example we have 2 sets of number to compare 20<30 (True), another 30<40 (True). Therefore if we used 20<30 Eqv 30<40 then the result is True and both False result is True. If both or either one is Null then result is Null. The details combination of Eqv logical test as below.
No. | If expression1 is | And expression2 is | The result is | ||||||||||||
1 | True | True | True | ||||||||||||
2 | True | False | False | ||||||||||||
3 | False | True | False | ||||||||||||
4 | False | False | True | ||||||||||||
5 | Null | True | Null | ||||||||||||
6 | False | Null | Null | ||||||||||||
7 | Null | Null | Null |
For more example refer below table and code.
VBA Vode:
Option Explicit
Sub ComparisonOperator_Eqv()
Dim i As Integer
Dim myRng As Range, Cell As Range
Dim iNum1, INum2, INum3, INum4
With ActiveSheet
i = 2
Do
If .Range("B" & i) = "" Then
iNum1 = Null
Else
iNum1 = .Range("B" & i)
End If
If .Range("C" & i) = "" Then
INum2 = Null
Else
INum2 = .Range("C" & i)
End If
If .Range("E" & i) = "" Then
INum3 = Null
Else
INum3 = .Range("E" & i)
End If
If .Range("F" & i) = "" Then
INum4 = Null
Else
INum4 = .Range("F" & i)
End If
.Range("D" & i) = iNum1 > INum2
.Range("G" & i) = INum3 > INum4
'Or operator
.Range("H" & i) = iNum1 > INum2 Eqv INum3 > INum4
i = i + 1
Loop While .Range("A" & i) <> ""
'To put background color base on result
Set myRng = .Range("H2:H8")
For Each Cell In myRng
If Cell.Text = "TRUE" Then
Cell.Interior.ColorIndex = 4 'Green
ElseIf Cell.Text = "FALSE" Then
Cell.Interior.ColorIndex = 6 'Yellow
Else
Cell.Interior.ColorIndex = 3 'Red
End If
Next
End With
End Sub
Sub ComparisonOperator_Eqv()
Dim i As Integer
Dim myRng As Range, Cell As Range
Dim iNum1, INum2, INum3, INum4
With ActiveSheet
i = 2
Do
If .Range("B" & i) = "" Then
iNum1 = Null
Else
iNum1 = .Range("B" & i)
End If
If .Range("C" & i) = "" Then
INum2 = Null
Else
INum2 = .Range("C" & i)
End If
If .Range("E" & i) = "" Then
INum3 = Null
Else
INum3 = .Range("E" & i)
End If
If .Range("F" & i) = "" Then
INum4 = Null
Else
INum4 = .Range("F" & i)
End If
.Range("D" & i) = iNum1 > INum2
.Range("G" & i) = INum3 > INum4
'Or operator
.Range("H" & i) = iNum1 > INum2 Eqv INum3 > INum4
i = i + 1
Loop While .Range("A" & i) <> ""
'To put background color base on result
Set myRng = .Range("H2:H8")
For Each Cell In myRng
If Cell.Text = "TRUE" Then
Cell.Interior.ColorIndex = 4 'Green
ElseIf Cell.Text = "FALSE" Then
Cell.Interior.ColorIndex = 6 'Yellow
Else
Cell.Interior.ColorIndex = 3 'Red
End If
Next
End With
End Sub
Note:
The above code can be simplify with array in advanced topic. This Eqv operator commonly use with If...Then..Else function.
Practice makes perfect. Thank You.
macro enabled excel
excel macro
vba coding
vba code
No comments:
Post a Comment