Comparison operator Like is for string versus pattern. The result after comparison is TRUE or FALSE, if TRUE then string matched with pattern and vice versa. Pattern consist character that represent certain condition as below.
Character in Pattern | Matches in string | ||
* | Nothing or Combination of any Characters. | ||
# | Any Single digit number. | ||
? | Any Single Character. | ||
[] | Any single character inside 2 characters ex: [A-Z] | ||
[!] | Any single character outside 2 Characters ex: [!A-Z] |
Below example is from Microsoft Reference -Like Operator.
Explanation:
No | Result | Explanation | ||||||
1 | True | "a" before and after Matched, "BBB" Matched with "*" | ||||||
2 | True | "F" Matched single Character within "[A-Z]" | ||||||
3 | False | "F" Not matched other than Character within "[!A-Z]" | ||||||
4 | True | "a" before and after Matched, "2" Matched with "#" | ||||||
5 | True | "a" Matched, "M" Matched [L-P], "5" Matched "#", "b" Matched "[!c-e]" | ||||||
6 | True | "B" Matched, "A" Matched "?", T Matched, "123khg" Matched with "*" | ||||||
7 | False | "B" Not matched "C", "A" Matched "?", T Matched, "123khg" Matched with "*" | ||||||
8 | True | "a" Matched, Nothing Matched with "*", "b" Matched | ||||||
9 | False | "a" Not matched with "a ", "*" Matched "[*]", "b" Matched | ||||||
10 | False | "a" Matched, "xxxxx" Not matched "[*]", "b" Matched | ||||||
11 | True | "a " Matched, "[" Matched "[[]", "xyz" Matched "*" | ||||||
12 | Error :93 | Incomplete pattern (Error 93) |
VBA Code:
Option Explicit
Sub ComparisonOperator_Like()
Dim i As Integer
Dim StrText As String
Dim StrPtrn As String
Dim Cell As Range, MyRng As Range
With ActiveSheet
i = 2
Do
'Assign Variables
StrText = .Range("B" & i)
StrPtrn = .Range("C" & i)
On Error Resume Next
'Compare string and pattern with operator like
.Range("D" & i) = StrText Like StrPtrn
'Indicate Error Code if failed
If Err.Number <> 0 Then
.Range("D" & i) = "Error :" & Err.Number
End If
'Reset Variables
Err.Clear
StrText = "": StrPtrn = ""
i = i + 1
Loop While .Range("A" & i) <> ""
'To put background color base on result
Set MyRng = .Range("D2:D13")
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_Like()
Dim i As Integer
Dim StrText As String
Dim StrPtrn As String
Dim Cell As Range, MyRng As Range
With ActiveSheet
i = 2
Do
'Assign Variables
StrText = .Range("B" & i)
StrPtrn = .Range("C" & i)
On Error Resume Next
'Compare string and pattern with operator like
.Range("D" & i) = StrText Like StrPtrn
'Indicate Error Code if failed
If Err.Number <> 0 Then
.Range("D" & i) = "Error :" & Err.Number
End If
'Reset Variables
Err.Clear
StrText = "": StrPtrn = ""
i = i + 1
Loop While .Range("A" & i) <> ""
'To put background color base on result
Set MyRng = .Range("D2:D13")
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
Practice makes perfect. Thank You.
No comments:
Post a Comment