Table of Contents
In VBA, you can compare strings using the StrComp function. This function takes two strings as parameters and returns one of three values: 0 (the strings are equal), -1 (the first string is less than the second string) or 1 (the first string is greater than the second string). For example, you can compare two strings using the below code: StrComp(“Apple”, “Banana”) which would return -1 as the first string is less than the second string.
You can use the following methods in VBA to compare strings:
Method 1: Case-Sensitive String Comparison
Sub CompareStrings()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = StrComp(Range("A" & i), Range("B" & i)) = 0
Next i
End Sub
This macro will perform a case-sensitive string comparison between the strings in the corresponding cells in the ranges A2:A10 and B2:B10 and return TRUE or FALSE in the range C2:C10 to indicate whether or not the strings are equal.
Method 2: Case-Insensitive String Comparison
Sub CompareStrings()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = StrComp(Range("A" & i), Range("B" & i), vbTextCompare) = 0
Next i
End Sub
This macro will perform a case-insensitive string comparison between the strings in the corresponding cells in the ranges A2:A10 and B2:B10.
The following examples show how to use each method in practice with the following lists of strings in Excel:
Example 1: Case-Sensitive String Comparison in VBA
We can create the following macro to perform a case-sensitive string comparison between each corresponding string in columns A and B:
Sub CompareStrings()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = StrComp(Range("A" & i), Range("B" & i)) = 0
Next i
End Sub
When we run this macro, we receive the following output:
Column C returns TRUE if the strings are equal and have the same case.
Otherwise, column C returns FALSE.
Example 2: Case-Insensitive String Comparison in VBA
Sub CompareStrings()
Dim i As Integer
For i = 2 To 10
Range("C" & i) = StrComp(Range("A" & i), Range("B" & i), vbTextCompare) = 0
Next i
End Sub
When we run this macro, we receive the following output:
Column C returns TRUE if the strings are equal, regardless of the case.
Column C only returns FALSE if the strings are not equal.
Note: You can find the complete documentation for the StrComp function in VBA .