Use of the WorksheetFunction INDEX and MATCH

[RobertaSV]

Hello everyone.
I'm writing a code in VBA to calculate pricing for products here at my job. There are several tables with the pricing values, and when the user enters some criteria in the Form, the code searches for the sheet, table, row and column that match the criteria and gives back the value of the matching cell.
However, I'm having an issue with finding specifically the matching column.
I'm using the combination of the INDEX and MATCH functions, but when I run the form, I get back the message: "Run-time error '1004': Unable to get the Match property of the WorksheetFunction class".
Below is the part of the code that I am trying to run.
The variables with a cmb before them are the values selected by the user in the combination boxes in the Form. They are the criteria that locate the wanted value. "vSize" would be the index of each table inside the sheet, indicating wich table is which (also one of the criteria).


Option Explicit
Dim W As Worksheet
Dim Size As Variant
Dim Thickness As Variant
Dim NParts As Variant
Dim vSize As Range
Dim SelectedTable As Range

Private Sub btCalculate_Click()

If cmbSheetType = ("Crystal") Then


Set W = Sheets("Crystal Sheet Price")
W.Activate

Size = cmbSheetSize
Thickness = cmbThickness
NParts = cmbParts


With Application.WorksheetFunction
Set vSize = W.Range("B:B").Find(Size, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With

Set SelectedTable = W.Range(vSize.Offset(1, 1), vSize.Offset(5, 10))

With Application.WorksheetFunction

W.Range("M26").Value = .Index(SelectedTable, .Match(NParts, _
W.Range(vSize.Offset(1, 0), vSize.Offset(5, 0)), 0), .Match(Thickness, _
W.Range(vSize.Offset(0, 1), vSize.Offset(0, 10)), 0))

End With

End If

End Sub


After a few tests I realized the error ocurred specifically at the part ".Match(Thickness, W.Range(vSize.Offset(0, 1), vSize.Offset(0, 10)), 0)", which does not work when I try to run it "on its own".
I apologize if I wasn't clear or if I made any mistake. I am new to this forum. But I thank in advance anyone who might give this problem a look.

[Paul_Hossler]

Can you post a small sample workbook?

[RobertaSV]

Here, I've attached a sample workbook that is giving me the same error.

[Paul_Hossler]

I simplified your logic a bit since I was getting tangled up with all the .Indexes and .Matchs and .Ranges

PS -- you have 1.5mm as a selection, but no pricing for that thickness




Private Sub btCalculate_Click()
Dim rowNumb As Long, colThick As Long, rowSize As Long

If cmbSheetType = ("Crystal") Then
Set W = Sheets("Crystal Sheet Price")
Else
Set W = Sheets("Color Sheet Price")
End If

'or skipping the If/Than/Else
' Set W = Sheets(cmbSheetType & " Sheet Price")

Size = cmbSheetSize
Thickness = cmbThickness
NParts = cmbParts


With Application.WorksheetFunction

rowSize = .Match(Size, W.Columns(2), 0)
Set SelectedTable = W.Cells(rowSize, 2).CurrentRegion

rowNumb = .Match(NParts, SelectedTable.Columns(2), 0)
colThick = .Match(CDbl(Thickness), SelectedTable.Rows(2), 0)
W.Range("M3").Value = SelectedTable.Cells(rowNumb, colThick).Value
End With


End Sub