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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
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,406
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
53,317
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,388
Messages
5,601,359
Members
414,447
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top