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 luck sorting through all the various iterations of VBA syntax in the last 10+ years...

Please help! Thanks.

Tim Williams · Accepted Answer · 2012-07-19 21:26:05Z

5

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

answered Jul 19, 2012 at 21:26

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

diakonos1984 Over a year ago

And this is the solution to the next step I'm going to take. Thanks for thinking ahead.

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-07-19 21:06:59Z

2

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

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

answered Jul 19, 2012 at 21:06

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

1 Comment

diakonos1984 Over a year ago

So THAT's what the ampersand is for! I thought some people were using it to indicate a variable in older versions. That was the solution I needed! Thanks a lot.

user3564289 · Accepted Answer · 2014-04-23 11:18:39Z

0

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

answered Apr 23, 2014 at 11:18

user3564289

1

Collectives™ on Stack Overflow

Reference a table column by variable

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related