Match Index in VBA error

Question

I am trying to use the Index and Match Worksheet Functions to match an item number from a combo box in a user form (ItemNum.value) to a product list table ("Product Pricing") on a worksheet. Then pull cells that match the same row from that worksheet ("Product Pricing") and copy them to cell ("i4") on an input data worksheet ("Review Lighting Data"). The problem is every time I run the macro it gives me the error "1004 Unable to get the Match Property of the WorksheetFunction class error"

My code is:

Private Sub InputLight_Click()
With Sheets("Review Lighting Data")
    .Range("i4").Value = Application.WorksheetFunction.Index(Sheets("Product Pricing").Range("c7:c102"), Application.WorksheetFunction.Match(ItemNum.Value, Sheets("Product Pricing").Range("b7:b102"), 0))
End With
End Sub

I've since tried starting a new workbook to try to simplify things. Here is my complete code so far:

Private Sub inputbutton_Click()
Dim MatchRow As Long
Dim WS0 As Worksheet, WS1 As Worksheet
Dim R0 As Range, R1 As Range
With ThisWorkbook
    Set WS0 = .Sheets("Review Lighting Data")
    Set WS1 = .Sheets("Product Pricing")
End With
With WS1
    Set R0 = WS1.Range("B7:B11")
    Set R1 = WS1.Range("C7:C11")
End With
MatchRow = Application.Match(itemnum.Value, R0, 0)
MsgBox MatchRow

End Sub


Private Sub UserForm_Initialize()

With Me.itemnum
.AddItem "1001"
.AddItem "1002"
.AddItem "1003"
.AddItem "1004"
.AddItem "1005"
End With

End Sub

I've entered the item numbers into the combobox (itemnum) EXACTLY with no spaces or anything. I've even tried deleting the " marks around each number but that doesn't work either. I've tried outputting (MatchRow) into a MsgBox to try to catch the error but it breaks before it does.

Answer 1

The problem is that when Match doesn't found value in range, it returns error. You can handle this situation using IsError:

Private Sub InputLight_Click()
    Dim matchRes

    With Sheets("Review Lighting Data")
        matchRes = Application.Match(ItemNum.Value, Sheets("Product Pricing").Range("b7:b102"), 0)
        If Not IsError(matchRes) Then
            .Range("i4").Value = Application.Index(Sheets("Product Pricing").Range("c7:c102"), matchRes)
        End If
    End With
End Sub

Answer 2

One good practice is to qualify everything properly. This makes the code easier to read and to debug. Also, you have to use Application.Match and not Application.WorksheetFunction....

Private Sub InputLight_Click()
    Dim MatchRow
    Dim WS0 As Worksheet, WS1 As Worksheet
    Dim R0 As Range, R1 As Range
    With ThisWorkbook
        Set WS0 = .Sheets("Review Lighting Data")
        Set WS1 = .Sheets("Product Pricing")
    End With
    With WS1
        Set R0 = .Range("B7:B102")
        Set R1 = .Range("C7:C102")
    End With
    MatchRow = Application.Match(ItemNum.Value, R0, 0)
    If Not IsError(MatchRow) Then
        WS0.Range("I4").Value = Application.Index(R1, MatchRow)
    End If
End Sub

A very similar issue is found here: Match Not working Excel: Error 1004 Unable to get the Match Property, wherein @simoco and I also dealt with it similarly.

Answer 3

Okay after trying a bunch of different ways to get match index working within the userform I decided to 'cheat' a little and just used the regular match index function in the cells. Thank you all so much for your help!