我如何循环使用他的名字&列参考？ [英] How do i loop an excel 2010 table by using his name & column reference?

问题描述

这是我的第一篇文章..我希望遵守规则。我搜索过&搜索，但没有提出任何答案。

This is my first post.. I hope to obey the rules. I've searched & searched but came up with no answer at all.

由于Excel 2010我在Excel中使用了很多表。
例如，我有一个表tabWorkers与3列：ID，名字，姓氏。

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".

我已经发现我可以使用[]参考VBA中的表。
例如：

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

当然，我可以做一些查找，将列索引'2'绑定到一个var像FirstnameIndex，但是我想要正确的语法。我确定这是可能的，但只是没有真正的记录（像[tabWorkers] .Rows）

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)

推荐答案

我不是很熟悉参考表的速记方法。如果您没有得到答案，您可能会发现这种使用ListOject模型的长方法很有用：

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

这篇关于我如何循环使用他的名字&列参考？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！