VBA Case sensitive Application.Match

I am trying to get a case sensitive match using Application.Match in Excel VBA.

I have read a bunch of posts that refer to formulas using INDEX and MATCH and EXACT but I need this to work in VBA... I have also had no luck using WorksheetFunction.Match Here is the code I need to modify to make it case sensitive. Thanks in advance

Sub Validate_Values2()
Dim i As Long, f As Variant
With Worksheets("Company") For i = 2 To .Cells(.Rows.Count, "M").End(xlUp).Row f = Application.Match(.Cells(i, "M").Value2, Worksheets("Lookups").Columns("U"), 0) If IsError(f) Then .Cells(i, "M").Interior.ColorIndex = 33 End If Next i End With End Sub

2 Answers

A direct string-to-string comparison in VBA is case sensitive with vbBinaryCompare. Why attempt to bend the wrong worksheet function to your will when VBA has the answer natively with the Filter function?

Option Explicit
Sub Validate_Values3() Dim i As Long, f As Long, comps As Variant, lookups As Variant With Worksheets("Lookups") 'skips a header row in Lookups!:U:U 'creates a zero-based 1-D array lookups = Application.Transpose(.Range(.Cells(2, "U"), .Cells(.Rows.Count, "U").End(xlUp)).Value2) End With With Worksheets("Company") 'skips a header row in Company!:M:M 'creates a one-based 2-D array comps = .Range(.Cells(2, "M"), .Cells(.Rows.Count, "M").End(xlUp)).Value2 For i = LBound(comps, 1) To UBound(comps, 1) 'Debug.Print comps(i, 1) & " " & UBound(Filter(lookups, comps(i, 1), True, vbBinaryCompare)) f = UBound(Filter(lookups, comps(i, 1), True, vbBinaryCompare)) If f < 0 Then 'shift down one row to account for skipped header .Cells(i + 1, "M").Interior.ColorIndex = 33 End If Next i End With
End Sub

Function matchCaseSensitive(lookupV As Variant, lookupA As Variant, Optional MatchType As Variant = 2, _ Optional LookIn As Variant = xlValues, _ Optional LookAt As Variant = xlWhole, _ Optional SearchOrder As Variant = xlByRows, _ Optional SearchDirection As Variant = xlNext, _ Optional MatchCase As Variant = True) 'like Application.WorksheetFunction.match but case sensitive for String in lookupV and Range in lookupA On Error GoTo Error If VarType(lookupA) < vbArray Then GoTo Error If MatchType = 2 Then If VarType(lookupV) = vbString And TypeName(lookupA) = "Range" Then matchCaseSensitive = lookupA.Find( _ What:=lookupV, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=SearchOrder, _ SearchDirection:=SearchDirection, _ MatchCase:=MatchCase).Row - lookupA.Row + 1 Exit Function End If MatchType = 0 End If matchCaseSensitive = Application.WorksheetFunction.match(lookupV, lookupA, MatchType) Exit Function
Error: matchCaseSensitive = CVErr(2042)
End Function