Getting the "Unable to get the Match property of the WorksheetFunction class" error

Question

I am getting the mentioned error when executing the following line of code:

Check_Row = Application.WorksheetFunction.Match(ActiveCell, Application.WorksheetFunction.Index(Selection, 0, 1), 0)

Both my ActiveCell and Selection seem to be correctly defined - ActiveCell contains some text, and the first Column of Selection contains a cell with the value match of ActiveCell. Also, when I try to run almost the same code (below) in a freshly opened excel sheet, it is working fine?

Sub Test()

Range("start").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
test_selection = Selection

Range("A8").Select

Check_Row = Application.WorksheetFunction.Match(ActiveCell,Application.WorksheetFunction.Index(test_selection, 0, 1), 0)

Range("A9").Value = Check_Row

End Sub

Does anyone have an idea what I might be overlooking here? What could be causing this issue?

Put `Option Explicit` on top. Declare all variables. Have a look at https://stackoverflow.com/a/27302888/11683, then https://stackoverflow.com/q/10714251/11683. — GSerg, Jan 23 '19 at 12:24
Other than that, the code should work. It probably does what you did not expect it to do (e.g. `test_selection = Selection` copies the cells' values into an array instead or remembering reference to the range), but it works. If it does not for you, it is only because `Match` does not find anything. — GSerg, Jan 23 '19 at 12:56

Tom · Answer 1 · 2019-01-23T13:31:45.520

1

You can achieve what you're trying to do using .Columns(1) instead of indexing the Range. I've updated your code as well to avoid using Select statements. You should update the first With with a proper reference to the sheet that this is on instead of using ActiveSheet

Option Explicit
Sub Test()
    Dim test_selection As Range
    Dim Check_Row As Long

    ' Update with your sheet reference
    With ActiveSheet
        set test_selection = .Range("start").CurrentRegion

        Check_Row = WorksheetFunction.Match(.Range("E17").Value2, test_selection.Columns(1), 0)

        .Range("E25").Value = Check_Row
    End With
End Sub

edited Jan 23 '19 at 13:31

answered Jan 23 '19 at 12:26

Tom

9,725
3
31
48

Why use `Transpose` at all? `Match` is okay with either rows or columns. – GSerg Jan 23 '19 at 12:28
I was incorrectly assuming there was a reason for the `Index` but you're right – Tom Jan 23 '19 at 12:30
Now you're incorrectly assuming Match can accept a 2D array or range; it cannot. – GSerg Jan 23 '19 at 12:34
@GSerg Had noticed too - You beat me to updating my answer. Works now – Tom Jan 23 '19 at 12:39
1

Also the entire `.End()` business, given that we start from top left, can be replaced with `.Range("start").CurrentRegion`. And while coding it this way instead of with the `Index()` is arguably better, the OP's original code should work too. – GSerg Jan 23 '19 at 12:52
@GSerg Didn't know about `CurrentRegion` that's a handy property – Tom Jan 23 '19 at 13:29

Getting the "Unable to get the Match property of the WorksheetFunction class" error

1 Answers1