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.

score 1 · Answer 1 · answered Feb 25 '14 at 19:13

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

answered Feb 25 '14 at 19:13

Dmitry Pavliv

35,333
13
79
80

1

+1: Because this was exactly the same question you answered last time. :D – WGS Feb 25 '14 at 19:20
:) I'm in fire and it's would be faster to write new answer rather than find link to previous one:) – Dmitry Pavliv Feb 25 '14 at 19:21
1

True! I actually had the text pat down in my answer box when I saw `1 new answer...` while looking for the link. I immediately thought, this *has* to be simoco. Lol. – WGS Feb 25 '14 at 19:22

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

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.

edited May 23 '17 at 10:26

Community

1
1

answered Feb 25 '14 at 19:17

WGS

13,969
4
48
51

It's returning a type mismatch error at this point: MatchRow = Application.Match(ItemNum.Value, R0, 0) – Giraffe Feb 25 '14 at 20:08
Error is possibly because `MatchRow` is supposed to be `Long` but is returning an error. Removed the `As Long` part. See if it works now. – WGS Feb 25 '14 at 20:15
Now it doesn't output anything. I added an Else Msgbox "Error" and that triggers so it's still encountering an error at Matchrow = Application... Could there be an error with how I'm adding items to the combo box? I'm using With me.ItemNum .AddItem "1001" etc in Userform_Initialize – Giraffe Feb 25 '14 at 20:28
Check if the value in `Range("I4")` is ***exactly*** the same as the one in `R0`. – WGS Feb 25 '14 at 20:42
Range("I4") is a blank cell. I want to input the value found in R1 that matches the R0 row that has the item number input into itemnum.value – Giraffe Feb 25 '14 at 20:46
My mistake. The value in `ItemNum`, I mean. – WGS Feb 25 '14 at 20:47
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. – Giraffe Feb 26 '14 at 13:58

score 0 · Answer 3 · answered Feb 26 '14 at 15:43

0

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!

answered Feb 26 '14 at 15:43

Giraffe

55
1
9

Match Index in VBA error

3 Answers3