Returning a column number from a table using the =COLUMN function

Question

I have the following function in Excel that returns the column number of a table based on the column header. The table is called Config and the table column header is value. The below will return to me the column number in excel.

=COLUMN(Config[Value])-COLUMN(Config)+1

Could anybody let me know how this can be used in VBA? I was hoping i could use something like ...

Application.WorksheetFunction.Column

but it seems Column is not available for me to use.

Would anyone have any ideas on this? Any help would be greatly appreciated.

Answer 1

Since you're using a table (ListObject), you can refer to a ListColumn by its header. If you need the column number, you can use ListColumn.Range.Column - something like this.

Option Explicit

Sub Test()
    Dim lc As ListColumn
    Dim col As Long

    Set lc = Sheets("Sheet1").ListObjects("Table1").ListColumns("Data1")
    col = lc.Range.Column
    Debug.Print col
End Sub

Answer 2

I used a much simpler method:

=COLUMNS(X[[#Headers],[A]:[B]])

Where:
 X is the Table name
 A is the name of the 1st column 
 B is the name of the column you need.

This formula is dynamic