CODEWITHSUNDEEP
excelvba
diakonos1984
diakonos1984

Reputation: 53

Reference a table column by variable

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.

Upvotes: 5

Views: 20063

Answers (3)

user3564289
user3564289

Reputation: 1

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

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166146

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

Upvotes: 5

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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

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

Upvotes: 2

Related Questions