VBA Index-Match with List.Object

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
83
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)

VBA Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
WS1.Cells(r, c).Value = WorksheetFunction.Index(ColesScanSales, WorksheetFunction.Match(Vlkup, ColesScanSales.ListColumns(3), 0), 1)
 

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
83
Try it like
VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Try...

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

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
83

ADVERTISEMENT

Try...

VBA Code:
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

Board Regular
Joined
Sep 19, 2018
Messages
83
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,021
Messages
5,575,616
Members
412,679
Latest member
TSpan
Top