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

Question

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

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

我知道如果没有匹配，MATCH() 函数返回#N/A，因此没有必要将其分配给 INDEX 变量(此外，我认为这也可能导致错误)。

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

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

Answer 1

1. 使用 Application.Match 而不是 Application.WorksheetFunction.Match。该错误表明 Match 本身丢失，而不是 Match 的参数有问题。 (不过，不知道为什么 Match 应该丢失!)

2. 正如您在评论中提到的，Dim Index as Variant 而不是 Integer。 (顺便说一句，use Long instead of Integer，除非您调用仅 16 位的 API 函数。)

3. 每this answer ，如果匹配失败，Application.Match 将返回错误 Variant (#N/A)。要测试这一点，请使用 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