Reference a table column by variable

Question

This should be simple, but I'm a VBA noob. I've read lots of forums, and found nothing but overly complicated code that I couldn't decipher to fit my application. In Excel 2007 I have a Table already defined. I can't post a picture because I am new to the forum, but the table has 3 columns, with header rows named 1 through 3, and one data row as crudely shown below: ``` Table1 +------+------+-------+ |1 | 2 | 3 | +------+------+-------+ |Alpha |Bravo |Charlie| +------+------+-------+ ``` With this simple Table the following works, and returns the text "Alpha". ``` Sub works() Dim item As String Sheets("Sheet1").Select item = ActiveSheet.Range("Table1[1]") MsgBox (item) End Sub ``` But I want to be able to refer to table column headers with an adjustable variable. Why doesn't this work: ``` Sub doesntwork() Dim item As String Dim i As String i = 1 Sheets("Sheet1").Select item = ActiveSheet.Range("Table1[i]") MsgBox (item) End Sub ``` It's got to be a syntax thing, but I'm having no luc…

Accepted Answer

If you want to refer to the Headers of a structured table then this would get you a reference to the range containing the headers:

```
Dim rng as Range    
Set rng = Activesheet.Range("Table1[#Headers]")
```

...and if you want to refer to a specific header by position:

```
Dim rngHdr2    
Set rngHdr2 = rng.Cells(2) '2nd header cell
```

Reference a table column by variable

Solution

Problem