2

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
Community
  • 1
  • 1
Oleksandr Titorchuk
  • 69
  • 1
  • 2
  • 7
  • What version of Excel? You could try `Application.Match` instead of `Application.WorksheetFunction.Match` per [this](https://stackoverflow.com/questions/20028769/match-function-vba-not-working-properly?rq=1). **PS** Welcome to the site! Please check out the [tour](https://stackoverflow.com/tour) for more info about asking questions that will attract quality answers. – cxw Jul 15 '16 at 14:23
  • 1
    Thank you for reply! I am using Excel 2010. Your tip actually helped, but only when I changed declaration of "Index" variable - from Integer type to the Variant (otherwise I got _Type Mismatch_ error). I think it relates to the potential issue I mentioned in my question - if MATCH() function at some point returns "#N/A" instead of a number you get an error - because you explicitly said that Index can only be Integer. The problem that remains is that this code will not run further unless I handle the possibility of getting "#N/A" from MATCH(). So, I will think now what to do about it. – Oleksandr Titorchuk Jul 15 '16 at 14:50
  • I think you can just use cell instead of CStr(cell.value), strange this is a function I use and it works well for me. I usually get it working in excel first in a formula, then change that formula into vba code afterwards. – Lowpar Jul 15 '16 at 15:10

1 Answers1

3

  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
Community
  • 1
  • 1
cxw
  • 16,685
  • 2
  • 45
  • 81
  • https://msdn.microsoft.com/en-us/library/office/ff835873.aspx expression .Match(Arg1, Arg2, Arg3) expression A variable that represents a WorksheetFunction object. it has to be `application.worksheetfunction.match()` – Gareth Jul 15 '16 at 15:35
  • @Gareth I saw that, too, but [OP says](http://stackoverflow.com/questions/38398310/match-function-in-vba/38399444?noredirect=1#comment64206499_38398310) `Application.Match` worked. See also the linked answer. I don't have 2010 available, so wasn't able to test there. On my 2013 install, `Application.Match` does exist, even though it doesn't show up in the Object Browser! – cxw Jul 15 '16 at 15:43
  • Thank you all very much! This is exactly what I looked for. – Oleksandr Titorchuk Jul 15 '16 at 15:51