VBA Index-Match with List.Object

[Guinaba]

Hi guys,

Not sure what I am missing in the index-match formula below, I am using ListObject for my range (table).

ws1.Cells(r, c).Value = WorksheetFunction.Index(Range("ColesScanSales"), WorksheetFunction.Match(Vlkup, Range("ColesScanSales").ListColumns(3), 0), 1)

Option Explicit
Public Sub Example()
   
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Template")

    Dim LRow As Integer
    LRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).row
   
    Dim LCol As Integer
    LCol = ws1.Cells(5, ws1.Columns.Count).End(xlToLeft).Column
   
    Dim VlkupRng As Range
    Set VlkupRng = ws1.Range("A5", ws1.Cells(LRow, LCol))
   
    'Declaring worksheet to read scandata
    Dim ws4 As Worksheet
    Set ws4 = ThisWorkbook.Worksheets("ColesScanData")

    Dim ColesScanSales As ListObject
    Set ColesScanSales = ws4.ListObjects("tColesScanSalesChilled")
     
   
    Dim r As Long
    Dim c As Long
    Dim Vlkup As Variant
   
    For r = 6 To VlkupRng.Rows.Count
       
        For c = 6 To VlkupRng.Columns.Count
       
        Vlkup = ws1.Cells(r, 2) & ws1.Cells(r, 5) & Format(ws1.Cells(5, c), "d/mm/yyyy")
       
            If Cells(r, 5).Value = "Coles Scan Sales" Then

                ws1.Cells(r, c).Value = WorksheetFunction.Index(Range("ColesScanSales"), WorksheetFunction.Match(Vlkup, Range("ColesScanSales").ListColumns(3), 0), 1)
           
            End If
   
        Next c
       
    Next r
   
End Sub

 

[Fluff]

Try it like

WS1.Cells(r, c).Value = WorksheetFunction.Index(ColesScanSales, WorksheetFunction.Match(Vlkup, ColesScanSales.ListColumns(3), 0), 1)

 

[Guinaba]

Try it like

WS1.Cells(r, c).Value = WorksheetFunction.Index(ColesScanSales, WorksheetFunction.Match(Vlkup, ColesScanSales.ListColumns(3), 0), 1)

Thanks @Fluff for replying, I’ve tried your suggestion but I’m getting the Run-time error 1004: Unable to get the Match property of the worksheet function class.

 

[Domenic]

Try...

ws1.Cells(r, c).Value = WorksheetFunction.Index(ColesScanSales.DataBodyRange, WorksheetFunction.Match(Vlkup, ColesScanSales.ListColumns(3).DataBodyRange, 0), 1)

 

[Guinaba]

Try...

ws1.Cells(r, c).Value = WorksheetFunction.Index(ColesScanSales.DataBodyRange, WorksheetFunction.Match(Vlkup, ColesScanSales.ListColumns(3).DataBodyRange, 0), 1)

Thanks @Domenic! But still getting the same error

 

[Guinaba]

Hi guys I have replaced WorksheetFunction.Match for Application.Match and it is working now. Using the Application.Match function which allows for better ability to trap errors. When using the WorksheetFunction.Match, when a match is not found, it returns an error, which is I'm experiencing now.

 

[Fluff]

Glad you sorted it & thanks for the feedback.