Variable table column reference

Question

It should be simple, but I'm a VBA noob. I read a lot of forums and found nothing but code that was too complicated, which I could not decrypt to fit my application.

In Excel 2007, I already have a table. I cannot post the image because I am new to the forum, but the table has 3 columns with headers with names from 1 to 3 and one row of data, as shown below:

Table1 +------+------+-------+ |1 | 2 | 3 | +------+------+-------+ |Alpha |Bravo |Charlie| +------+------+-------+ 

Using this simple table, the following steps are performed and the text "Alpha" is returned.

 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 a variable. Why this does not 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 

This should be the syntax, but I have not been able to sort all the various iterations of the VBA syntax over the last 10 years ...

Please, help! Thanks.

Answer 1

If you want to refer to the headers of the structured table, this will give you a link 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 heading by position:

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

Answer 2

You need to use concatenation to construct the table name as a string:

 item = ActiveSheet.Range("Table1[" & i & "]") 

Answer 3

combining both answers

Sub Demo ()

 Dim rng As Range Dim celda As Range Dim nroTabla As Integer Dim nroFila As Integer Dim nroCol As Integer nroTabla = 1 nroFila = 2 nroCol = 3 Set rng = ActiveSheet.Range("Tabla" & nroTabla & "[#Headers]") Set celda = rng.Cells(nroFila, nroCol) celda.Value = 700 

End Sub