excel - 无法检查数组中是否存在值

Question

在尝试检查数组中是否存在值If Not IsError(Application.Match(arrString, RegM, 0)) Then... 但由于某种原因它不起作用。我也尝试了以下但没有成功Application.WorksheetFunction.CountIf(DirArray2, RegM)

当我检查 msgbox 中的值时，一切似乎都正常，但仍然无法正常工作（下面的 msgbox 显示例如“DMM | DMM|TEST”

MsgBox RegM & " | " & Application.WorksheetFunction.CountIf(DirArray2, RegM)

我引用了整个函数，因为也许你在代码中找到了导致问题的东西

Sub mk_RegExp()
    If Sheets("MISC").Range("C62") = True Then
        Dim objRegex As Object
        Dim RegMC As Object
        Dim RegM As Object
        Dim item As Variant
        Dim DirArray As Variant
        Dim DirArray2 As Variant
        Dim DirArr As Variant
        Dim test As Variant
        '------------------------------------------------------------------------------------------------
        Sheets("LI").Range("C12:DJ42").Font.Color = vbBlack
        '------------------------------------------------------------------------------------------------
        arr = Sheets("MISC").Range("R4:R145").Value
        LastRow = Sheets("MISC").Cells(Rows.Count, "L").End(xlUp).Row
        DirArray = Join(Application.Transpose(Sheets("MISC").Range("L4:L" & LastRow).Value), "|")
        LastRow2 = Sheets("MISC").Cells(Rows.Count, "M").End(xlUp).Row
        DirArray2 = Join(Application.Transpose(Sheets("MISC").Range("M4:M" & LastRow2).Value), "|")
        arrString = Join(Application.Transpose(Sheets("MISC").Range("M4:M" & LastRow2).Value), ",")
        DirArr = DirArray & "|" & DirArray2
        '------------------------------------------------------------------------------------------------
        Set objRegex = CreateObject("vbscript.regexp")
        With objRegex
            .Global = True
            .Pattern = DirArr

           For Each item In arr
                If .test(Range(item).Value) Then
                    Set RegMC = .Execute(Range(item).Value)
                    For Each RegM In RegMC
                        If Not IsError(Application.Match(arrString, RegM, 0)) Then
                            'MsgBox RegM & " | " & Application.WorksheetFunction.CountIf(DirArray2, RegM)
                            Range(item).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 176, 80)
                        ElseIf RegM = "COL" Or RegM = "CRT" Then
                            Range(item).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(0, 176, 240)
                        Else
                            Range(item).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = RGB(247, 150, 70)
                        End If
                    Next
                End If
            Next item
        End With
    Else
        Sheets("LI").Range("C12:DJ42").Font.Color = vbBlack
    End If
End Sub

标签： excelvba

Answer 1

除了我的评论

Sub test_array()

Dim a() As Variant

a = Application.Transpose(Range("a1:a5"))

Debug.Print Join(a, "|")

Debug.Print Application.WorksheetFunction.Match("Test 2", a, 0)
Debug.Print Application.WorksheetFunction.Match("Test 2", Join(a, "|"), 0)


End Sub

第二行错误，第一行没问题。