如何在VBA中应用MATCH()函数? [英] How to apply MATCH() function in VBA?

问题描述

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

I am trying to run the code below but get

错误1004:无法获取WorksheetFunction类的Match属性.

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

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

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).

我如何解释这种可能性?

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

推荐答案

1. 使用Application.Match代替Application.WorksheetFunction.Match.该错误表明Match本身已丢失，而不是Match的参数有问题. (不过，不确定为什么Match应该丢失！)

1. Use Application.Match instead of Application.WorksheetFunction.Match. The error indicates that Match itself is missing, not that the arguments to Match are problematic. (Not sure why Match should be missing, though!)

正如您在评论中提到的，Dim Index as Variant而不是Integer. (偶然地，使用Long而不是Integer ，除非您要调用仅16位的API函数. )

As you mentioned in your comment, Dim Index as Variant rather than as Integer. (Incidentally, use Long instead of Integer unless you're calling a 16-bit-only API function.)

每个此答案，如果匹配失败(#N/A)，Application.Match返回错误Variant .要对此进行测试，请使用IsError:

Per this answer, Application.Match returns 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

这篇关于如何在VBA中应用MATCH()函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！