Unable to get Match property of the WorksheetFunction class

Question

What I'm trying to do is based on a Cell oSht_Input.Cells(Rows, 7) , I want to find a match in another Sheet's (periodSheet) Column A, and get the corresponding value based on a given column.

I have tried combining the usage of .Index and .Match to do this. the .Index works for me, however I'm getting and error that says

Run-time error '1004': Unable to get the Match property of the WorksheetFunction class.

I have also tried doing Application.Match in the codes, but that would return me a #N/A Value instead.

What am I doing wrong?

I'm a little bit confused the usage of Application.Match and Application.WorksheetFunction.Match.

Set oSht_Input = Worksheets(outSheet)
Set periodSheet = Worksheets("PeriodMetadata")
lastRow = oSht_Input.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Rows = 2 To lastRow
    With Application.WorksheetFunction
        dateCell = oSht_Input.Cells(Rows, 7)
        If rollupDataFile.GroupByPeriod Like "Week*" Then
            If rollupDataFile.GroupByPeriod Like "*Sunday" Then
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 1)
            ElseIf rollupDataFile.GroupByPeriod Like "*Monday" Then                    
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 2)

.... code continues

Edit: Decided to add in a little more context for better clarification based on the initial feedback.

dateCell will take in the value of a cell, which is definitely a date value. The Column A in periodSheet contains EVERY single date from January 1st 2000 to December 31st 2020, essentially covering all possible dates. You can safely assume that the value from the dateCell will be within this range of 20 years.

1. This is the oSht_Input, where Column G is the date

2. This is the PeriodMetadata sheet, where I am trying to match to Column A (which contains every single date of year 2000-2020 before finding the value based on the .index function.

Closely related: http://stackoverflow.com/questions/27302794/vba-match-type-mismatch — Jean-François Corbett, Apr 09 '15 at 07:07
@Jean-FrançoisCorbett that's because my previous exploration was on Android development, and most of the stuff given didn't really help solve my problem. But I do accept answers once they are of help/solve the problem, and thank everyone else for their inputs either way. If an upvote is what you really want as appreciation then fine, I'll just give it to you (not sure why this discussion is taking place, first time getting nitpicked on such things) — lyk, Apr 09 '15 at 07:37
@Jean-FrançoisCorbett noted. I'll explore further while waiting for more advice/solutions then, thanks — lyk, Apr 09 '15 at 07:44
@Jean-FrançoisCorbett Yes, doing that works in excel formula, so perhaps its a data formatting issue? — lyk, Apr 09 '15 at 07:50
Using the `Match()` function in VBA, I find that converting to `Long` when working with dates usually does the trick. — SierraOscar, Apr 09 '15 at 07:54
@SO yea I've thought about that. I've checked the custom formatting and it is still a date format. I've also tried setting Column A to Short Date, and did a CDate() function to the dateCell value, still doesn't work — lyk, Apr 09 '15 at 08:00
Try using `CLng()` instead. Excel stores dates as numbers, sometimes you have to bow to this fact in VBA. — SierraOscar, Apr 09 '15 at 08:01
@SO RIGHT! That seems to at least remove the error for now and I'm getting correct matches! Thanks so much for your help, your quick comment has been very useful (although I'm not sure why this is even needed, so unnecessary!) If you can put it as an answer I can help to accept (before I get flamed for being unappreciative again for adding "noise" with just thanks) — lyk, Apr 09 '15 at 08:09

score 4 · Accepted Answer · answered Apr 09 '15 at 08:09

4

Use CLng() or .Value2 instead when working with dates:

Sub SO()
'// C3 = "03/02/2015"
'// A1:A14 = "01/02/2015" to "14/02/2015"
'// All cells formatted as dates

'// This will NOT work:
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14"), 0)

'// This WILL work:
Debug.Print WorksheetFunction.Match(Range("C3").Value2, Range("A1:A14"), 0)

'// This WILL also work:
Debug.Print WorksheetFunction.Match(CLng(Range("C3").Value), Range("A1:A14"), 0)

End Sub

This is because of the way Excel stores dates as numbers - something which sometimes has to be accounted for in VBA

answered Apr 09 '15 at 08:09

SierraOscar

17,507
6
40
68

Yea seems to work for now, I just have to solve some minor logic issues now rather than the 1004 Error. Thank you so much once again! – lyk Apr 09 '15 at 08:11
Wha??? And amazingly, `Debug.Print WorksheetFunction.Match(Range("C3"), Range("A1:A14"), 0)` works, even though it should be implicitly using the default property, `.Value`. I don't get it. Has to be a bug. Either way, nicely spotted. – Jean-François Corbett Apr 09 '15 at 08:14
@Jean-FrançoisCorbett This is where I hit my knowledge boundary - I know that without specifying, VBA will use `.Value` as the default range method, but is there some level of evaluation going on there that produces the `Long` instead of the `Date` data type? - Possibly better suited to a chat than a comment admittedly. I'll try some tests, and maybe try debugging in VS and see what I get - If I find anything I'll update my answer accordingly :) – SierraOscar Apr 09 '15 at 08:17
@Jean-FrançoisCorbett follow-up: http://stackoverflow.com/questions/29533627/default-range-method-produces-unexpected-results – SierraOscar Apr 09 '15 at 08:35

score 3 · Answer 2 · edited May 23 '17 at 12:09

3

When no match is found, that's the error you get.

In the example below, for instance,

MsgBox WorksheetFunction.Match(4, Range("A1:A4"), 0)

works fine, returning "4" as expected, but

MsgBox WorksheetFunction.Match(5, Range("A1:A4"), 0)

throws the "Unable to get the Match property of the WorksheetFunction class" error.

enter image description here

Also see this note on how Worksheetfunction and Application behave differently.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 09 '15 at 06:59

Jean-François Corbett

37,420
30
139
188

Thanks for highlighting this, I have added extra information as you've suggested. Will try to work on more screenshots in the meantime to illustrate my problem better – lyk Apr 09 '15 at 07:10

Unable to get Match property of the WorksheetFunction class

2 Answers2

Linked