Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I loop an excel 2010 table by using his name & column reference?

Since Excel 2010 I'm using a lot of tables within Excel. For example, I have a table "tabWorkers" with 3 columns: "ID", "Firstname", "Lastname".

I already found out I can refer to a table in VBA using [].

For example:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns(2).Value)
Next

This will give me the Firstname of every row which works great. But I want to make it more dynamic by using the name of it's column like this:

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns("Firstname").Value)
Next

Of course I could make some kind of lookup that binds the column index '2' to a var like FirstnameIndex, but I want the correct syntax. I'm sure it's possible but just not really documented (like with [tabWorkers].Rows)

like image 705
Tiele Declercq Avatar asked Sep 19 '12 13:09

Tiele Declercq


Video Answer


2 Answers

I'm not very familiar with the shorthand method of referring to tables. If you don't get an answer, you might find this longhand method, that uses the ListOject model, useful:

Sub ListTableColumnMembers()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow

Set ws = ThisWorkbook.Worksheets(2)
Set lo = ws.ListObjects("tabWorkers")

For Each lr In lo.ListRows
Debug.Print Intersect(lr.Range, lo.ListColumns("FirstName").Range).Value
Next lr
End Sub
like image 55
Doug Glancy Avatar answered Oct 10 '22 17:10

Doug Glancy


try this:

Dim row as Range
For Each row in [tabWorkers[first name]].Rows
     MsgBox row.Value
Next
like image 30
Spencer Hutton Avatar answered Oct 10 '22 17:10

Spencer Hutton