Consulting

Results 1 to 4 of 4

Thread: Use of the WorksheetFunction INDEX and MATCH

  1. #1

    Use of the WorksheetFunction INDEX and MATCH

    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.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,993
    Location
    Can you post a small sample workbook?
    Last edited by Paul_Hossler; 11-07-2018 at 08:24 PM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,993
    Location
    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
    Last edited by Paul_Hossler; 11-08-2018 at 08:47 AM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •