vba 按标题名称和行号引用表格中的 Excel 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18811431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Refer to Excel cell in Table by header name and row number
提问by Ole Henrik Skogstr?m
I'm trying to refer to a cell in an excel table by using the table header name and the row number using VBA.
我试图通过使用 VBA 使用表标题名称和行号来引用 Excel 表中的单元格。
Is this posible?
这可能吗？
回答by brettdj
In your example, something like this:
在你的例子中，是这样的：
Dim tb As ListObject
'assumes Table is the first one on the ActiveSheet
Set tb = ActiveSheet.ListObjects(1)
MsgBox tb.DataBodyRange.Cells(2, tb.ListColumns("header4").Index)
回答by Salam Morcos
A shorter answer is:
一个简短的答案是：
MsgBox [MyTable].Cells(2, [MyTable[MyColumn]].Column)
Much cleaner and easier!
更干净，更容易！
回答by Mike Lasch
Is there any reason one should avoid using this method?
有什么理由应该避免使用这种方法吗？
ThisWorkbook.Worksheets("MyWksht").Range("TableName[ColumnTitle]").Cells(RowNumber)
ThisWorkbook.Worksheets("MyWksht").Range("TableName[ColumnTitle]").Cells(RowNumber)
Seems the simplest answer in my opinion.
在我看来，这似乎是最简单的答案。
回答by TomJohnRiddle
It seems to me that @Salam Morcos solution will not give a proper answer. If table starts from cell A2statment [MyTable[FirstColumnName]].Columnwould give value of 2. Proper solution would be:
在我看来，@Salam Morcos 解决方案不会给出正确的答案。如果表格从单元格A2语句开始，则[MyTable[FirstColumnName]].Column值为 2。正确的解决方案是：
MsgBox [MyTable].Cells(2, [MyTable].Column-[MyTable[MyColumn]].Column + 1)
