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
We can create the following macro to perform a case-insensitive 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), 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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Replace Characters in String
VBA: How to Remove Special Characters from String
VBA: How to Convert String to Integer