Case sensitive VBA Match

[PBezucha]

A recent thread showed the way how to do match with Exact function in Excel.
It reminded me of the need to improve my ancient VBA matches made clumsily by
sequel constraining the remainder of the (not very long) database or even
browsing it as a whole (first occurrence sufficient).

Unfortunately there is no WorksheetFunction.Exact – otherwise it would look
like this:

Function MatchRow(DBName As String, Sought As Variant) As Long
Dim DB As Range
With Worksheets(DBName)
Set DB = Range(.Range("A1"), .Range("A1").End(xlDown))
MatchRow = Application.WorksheetFunction.Match(True, _
Application.WorksheetFunction.Exact(Sought, DB), 0)
End With
End Function

Such a simple task should be very common, yet, and still I have not
succeeded in finding a smart DG solution. What are your better ways?

Sincerely

 

[Chip Pearson]

Use StrComp to compare two strings:

S1 = "abc"
S2 = "ABC"
If StrComp(S1, S2, vbBinaryCompare) = 0 Then
' exact match
Else
' not a match
End If

The vbBinaryCompare means that the match is case sensitive -- "abc" <>
"ABC". You can change it to vbTextCompare to make the comparison case
insensitive -- "abc" = "ABC".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jacob Skaria]

http://msdn.microsoft.com/en-us/library/9s233cfc(VS.80).aspx

' Defines variables.
Dim TestStr1 As String = "ABCD"
Dim TestStr2 As String = "abcd"
Dim TestComp As Integer
' The two strings sort equally. Returns 0.
TestComp = StrComp(TestStr1, TestStr2, CompareMethod.Text)
' TestStr1 sorts after TestStr2. Returns -1.
TestComp = StrComp(TestStr1, TestStr2, CompareMethod.Binary)
' TestStr2 sorts before TestStr1. Returns 1.
TestComp = StrComp(TestStr2, TestStr1)

 

[PBezucha]

Dear Chip,

Your statement is absolutely correct. Still it is an example of overdoing in
VBA. If I should find the row of exact equality, the eqality itself does the
job, no binary comparison is needed.

If I can ask again, has somebody a smarter solution to the problem of
finding the row of the first, case identical cell content than my recurrent
code:

Option Explicit
Function MatchRow(DBName As String, Sought As Variant) As Long
Dim DB As Range, FirstRow As Long, LastRow As Long
With Worksheets(DBName)
FirstRow = 1
LastRow = .Cells(FirstRow, 1).End(xlDown).Row
MatchRow = FirstRow
On Error GoTo ErrExit
Do
Set DB = Range(.Cells(MatchRow, 1), .Cells(LastRow, 1))
MatchRow = Application.Match(Sought, DB, 0) + MatchRow - FirstRow
If .Cells(MatchRow, 1).Value = Sought Then Exit Function 'IDENTITY TEST!
MatchRow = MatchRow + 1
Loop
End With
ErrExit:
End Function

Of course browsing with the same identity test runs OK too, but (how much?)
more slowly.

Thanks for your interest

 

[Jim Cone]

The "Find" method has a match case option.
--
Jim Cone
Portland, Oregon USA



"PBezucha"
<[email protected]>
wrote in message Dear Chip,
Your statement is absolutely correct. Still it is an example of overdoing in
VBA. If I should find the row of exact equality, the eqality itself does the
job, no binary comparison is needed.

If I can ask again, has somebody a smarter solution to the problem of
finding the row of the first, case identical cell content than my recurrent
code:

Option Explicit
Function MatchRow(DBName As String, Sought As Variant) As Long
Dim DB As Range, FirstRow As Long, LastRow As Long
With Worksheets(DBName)
FirstRow = 1
LastRow = .Cells(FirstRow, 1).End(xlDown).Row
MatchRow = FirstRow
On Error GoTo ErrExit
Do
Set DB = Range(.Cells(MatchRow, 1), .Cells(LastRow, 1))
MatchRow = Application.Match(Sought, DB, 0) + MatchRow - FirstRow
If .Cells(MatchRow, 1).Value = Sought Then Exit Function 'IDENTITY TEST!
MatchRow = MatchRow + 1
Loop
End With
ErrExit:
End Function

Of course browsing with the same identity test runs OK too, but (how much?)
more slowly.
Thanks for your interest