How to apply MATCH() function in VBA?

Question

I am trying to run the code below but get

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

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

Answer 1

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

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

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