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.

score 7 · Accepted Answer · answered Jul 10 '18 at 15:04

7

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

answered Jul 10 '18 at 15:04

BigBen

46,229
7
24
40

Thats awesome. Thank you. Is it possible to keep "Sheet1" dynamic. Like, in case someone changes the name of the sheet. I tried to use something like... `ActiveWorkbook.Sheets.ListObjects` but it does not seem to work. Would you know the syntax for this? – Geminiflipflop Jul 10 '18 at 15:30
[This answer](https://stackoverflow.com/questions/2649844/excel-tab-sheet-names-vs-visual-basic-sheet-names/2652524) might be useful - you could reference the sheet by its `CodeName`. [This](https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename) is a similar answer. – BigBen Jul 10 '18 at 15:38
Thanks for following up. I checked that like and i see that Sheet1 is a reference code that remains the same even if the sheet name changes. When i run the following however `Set lc = Sheets("Sheet1").ListObjects("Config").ListColumns("Value")`, i get a **Subscript out of range error**. Would you have any idea why this might be happening?? – Geminiflipflop Jul 10 '18 at 16:27
If you're using the `CodeName`, refer to `Sheet1` directly - `Sheet1.ListObjects...`, not `Sheets("Sheet1").ListObjects...`. – BigBen Jul 10 '18 at 16:31

score 0 · Answer 2 · answered Jul 10 '18 at 14:52

0

The syntax for getting the column would be something like

Worksheets("Sheet1").Range("B1").Column

to get the column number of cell B1.

If you use named ranges this will work too:

Worksheets("Sheet1").Range("NameOfRange").Column

answered Jul 10 '18 at 14:52

Pᴇʜ

56,719
10
49
73

Thanks for reply. I need to reference an actual table so if somebody was to move the table within the sheet it would be able to pick up the Value using the Table name and associated column header as a reference. Is this possible?? (apologies for not being clearer on that in my original post) – Geminiflipflop Jul 10 '18 at 14:59

score 0 · Answer 3 · edited Dec 20 '18 at 06:17

0

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

edited Dec 20 '18 at 06:17

Pradeep

9,667
13
27
34

answered Dec 20 '18 at 06:10

SMan

1

This question is about VBA, not about formulas. – BigBen Feb 02 '22 at 18:15

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

3 Answers3