Reference Tables in VBA

[Skybluekid]

Hi All,

I wonder if someone could point me in the right direction. I have an excel table, named Customers. In the table, there are two columns, Customer and PO, I would like reference that table within a VBA Index Function.

I have looked everywhere for an answer but I am not geting very far.

Could do with pointing in the right direction.

Thanks in Advance.

 

[Norie]

What in the table do you want to reference?

There are various ways to refer to tables in VBA, here are some examples.

Dim rngTbl As Range
Dim tbl As ListObject
Dim rngCol As Range
Dim tblCol As ListColumn

    Set rngTbl = Range("Customers") 'range reference to entire table
    Debug.Print rngTbl.Address
    Debug.Print
        
    Set tbl = ActiveSheet.ListObjects("Customers") ' list object reference to table
    Debug.Print tbl.Range.Address
    Debug.Print
    
    Set rngCol = Range("Customers[Customer]") ' range reference to 'Customer' column
    Debug.Print rngCol.Address
    Debug.Print
    
    Set tblCol = ActiveSheet.ListObjects("Customers").ListColumns("Customer") ' listcolumn reference to table
    Debug.Print tblCol.Range.Address
    Debug.Print

 

[MARK858]

Perhaps the link below might be worth a read.

https://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

 

[Skybluekid]

Thank you Norie for you help, it is now working.

 

[Skybluekid]

Thank you Mark858, it is a great link!