如何在 VBA 中应用 MATCH() 函数？

How to apply MATCH() function in VBA?

提问by Oleksandr Titorchuk

I am trying to run the code below but get

我正在尝试运行下面的代码，但得到

Error 1004: Unable to get the Match property of the WorksheetFunction class.

错误 1004：无法获取 WorksheetFunction 类的 Match 属性。

I understand if there is no match, MATCH() function returns #N/A, so there is no point to assign this to the INDEX variable (moreover, I think it might also cause an error).

我明白如果没有匹配，MATCH() 函数返回#N/A，所以没有必要将它分配给 INDEX 变量（此外，我认为它也可能导致错误）。

How can I account for this possibility?

我该如何解释这种可能性？

Sub Debugging()

Workbooks("Problem.xls").Worksheets(1).Activate

Cash_Rows = 5
Share_Rows = 6

If Cash_Rows <= Share_Rows Then

    Range("A1:A" & Cash_Rows).Select
    With Selection.Interior
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419 
    End With

    Count_Cash = Application.WorksheetFunction.CountIf(Range("A:A"), "L*")

    For Each cell In Range("A1:A" & Cash_Rows)
        If CStr(cell.Value) Like "L*" Then
            Range("A" & cell.Row & ":" & "D" & cell.Row).Interior.Color = 65535
            Dim Index As Integer
            Index = Application.WorksheetFunction.Match(CStr(cell.Value), Range("F2:" & "F" & Share_Rows), 0)
            Range("F" & Index & ":" & "I" & Index).Interior.Color = 65535
        End If
    Next

    If Count_Cash = 0 Then
        MsgBox "You do not have any matching ID+Amount between Cash and Shares booking. It's OK!"
    Else
        MsgBox "You have " & Count_Cash & " matching transactions. Check them!"
    End If 

Else 

    MsgBox "Do not worry. Be happy!" 

End If 

End Sub

采纳答案by cxw

1. Use Application.Matchinstead of Application.WorksheetFunction.Match. The error indicates that Matchitself is missing, not that the arguments to Matchare problematic. (Not sure why Matchshould be missing, though!)

2. As you mentioned in your comment, Dim Index as Variantrather than as Integer. (Incidentally, use Longinstead of Integerunless you're calling a 16-bit-only API function.)

3. Per this answer, Application.Matchreturns an error Variant if the match fails (#N/A). To test for that, use IsError:

   If Not IsError(Index) Then
       Dim idxstr as String: idxstr = CStr(Index)
           ' ^^ Make sure you don't get surprised by how the Variant converts
       Range("F" & idxstr & ":" & "I" & idxstr).Interior.Color = 65535
   End If
   

1. 使用Application.Match代替Application.WorksheetFunction.Match。错误表明它Match本身丢失了，而不是它的参数Match有问题。（不知道为什么Match会丢失，虽然！）

2. 正如您在评论中提到的，Dim Index as Variant而不是作为Integer. （顺便说一句，除非您正在调用仅 16 位的 API 函数，否则请使用Long代替Integer。）

3. 根据此答案，Application.Match如果匹配失败 ( #N/A) ，则返回错误 Variant 。要对此进行测试，请使用IsError：

   If Not IsError(Index) Then
       Dim idxstr as String: idxstr = CStr(Index)
           ' ^^ Make sure you don't get surprised by how the Variant converts
       Range("F" & idxstr & ":" & "I" & idxstr).Interior.Color = 65535
   End If