Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitive VBA Match
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 -- Petr Bezucha |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitive VBA Match
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) On Thu, 14 May 2009 05:19:01 -0700, PBezucha wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitive VBA Match
http://msdn.microsoft.com/en-us/libr...fc(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) -- If this post helps click Yes --------------- Jacob Skaria "PBezucha" wrote: 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 -- Petr Bezucha |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitive VBA Match
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 -- Petr Bezucha "Chip Pearson" wrote: 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) On Thu, 14 May 2009 05:19:01 -0700, PBezucha wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitive VBA Match
The "Find" method has a match case option.
-- Jim Cone Portland, Oregon USA "PBezucha" 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 -- Petr Bezucha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
Getting a case sensitive match? | Excel Worksheet Functions | |||
Case Sensitive w/ IF | Excel Worksheet Functions | |||
.Name case sensitive | Excel Programming | |||
can vlookup be forced to make a case sensitive match? | Excel Discussion (Misc queries) |