RobertaSV
11-07-2018, 11:27 AM
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.
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.