☰

VBA 区分大小写的 Application.Match

tag-icon • tag-icon microsoft-excel vba
VBA 区分大小写的 Application.Match

我正在尝试使用 Excel VBA 中的 Application.Match 获取区分大小写的匹配。

我读过很多帖子，其中提到了使用 INDEX 和 MATCH 以及 EXACT 的公式，但我需要这些公式在 VBA 中工作……我使用 WorksheetFunction.Match 也没有成功。以下是我需要修改的代码，以使其区分大小写。提前致谢

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

答案1

使用 vbBinaryCompare，VBA 中的直接字符串到字符串比较区分大小写。当 VBA 本身就有答案时，为什么要尝试按照您的意愿扭曲错误的工作表函数过滤功能？

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

答案2

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

相关内容