CODEWITHSUNDEEP
excelvba
Milan
Milan

Reputation: 289

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

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

enter image description here

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

Upvotes: 0

Views: 126

Answers (1)

Tom
Tom

Reputation: 9878

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

Upvotes: 1

Related Questions