VBA Blogs: Matching the Case
8 June 2018
Last week we used the LookAt parameter with the Find method to match the complete word.
Of the three methods of matching words listed last week, today we tackle the second method, matching the case of the word.
- Matching the complete word
- Matching the case of the word (i.e. the capitalisation)
- Matching a specific cell formatting
Similar to last week we are going to be matching words found in this field.
Matching the capitalized word
We want it to return with the cell C9 “chance UPON”. Let’s search for "UPON" from cell A1.
Sub FindMatchCase()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Dim foundrange As Range
Set foundrange = searchRange.Find("UPON", LookAt:=xlPart)
If foundrange Is Nothing Then
Debug.Print "not found!"
Else
Debug.Print foundrange
Debug.Print foundrange.Address
End If
End Sub
It found ‘stumble upon’ from cell E3. This suggests that we have to add another parameter: MatchCase. The MatchCase parameter will force VBA to match the case of the word. Let's search of "UPON" again from cell A1:
Sub FindMatchCase()
Dim searchRange As Range
Set searchRange = Range("A1:E10")
Dim foundrange As Range
Set foundrange = searchRange.Find("UPON", LookAt:=xlPart, MatchCase:=True)
If foundrange Is Nothing Then
Debug.Print "not found!"
Else
Debug.Print foundrange
Debug.Print foundrange.Address
End If
End Sub
This change in the code will give us the expected result of ‘chance UPON’ in cell C9.
A noteworthy part of the code to point out is that we setting the ‘LookAt’ parameter to ‘xlPart’ and not ‘xlWhole’. So if we wish to find the entire word we would have to change ‘xlPart’ to ‘xlWhole’.
Come back next week where we discuss how to match specific cell formatting.
