vba 如何在excel VBA中引用命名表列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28216633/
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
How to reference named table column in excel VBA?
提问by Gallaxhar
The syntax for using the Item property with the Cells property is as follows:
将 Item 属性与 Cells 属性一起使用的语法如下：
Cells.Item(Row,Column)
You must use a numeric value for Row, but you may use the numeric value or string value for Column. Both of the following lines refer to cell C5:
您必须为 Row 使用数值，但您可以为 Column 使用数值或字符串值。以下两行均指单元格 C5：
Cells(5,"C")
Cells(5,3)
Because the Item property is the default property of the RANGE object, you can shorten these lines as follows:
由于 Item 属性是 RANGE 对象的默认属性，您可以按如下方式缩短这些行：
Cells(5,"C")
Cells(5,3)
I recently discovered you can do this:
我最近发现你可以这样做：
For Each cell In Range("A2:A3")
Cells(cell.Row,3)
How can I do this (I need the correct syntax)
我该怎么做（我需要正确的语法）
For Each cell In Range("A2:A3")
Cells(cell.Row,cell.Row.NamedColumn"Customer")
回答by Isaac Moses
You should select Column E and assign a range nameto it of CUSTOMER. Then, you can refer to it in your code as Range("CUSTOMER")and to its column number as Range("CUSTOMER").Column. If you cut this column and paste it somewhere else, its name will come with it, so the references in your code will stay valid.
您应该选择E列，并指定一个范围名称给它CUSTOMER。然后，您可以在代码中将Range("CUSTOMER")其引用为 ，并将其列号引用为Range("CUSTOMER").Column。如果您剪切此列并将其粘贴到其他位置，它的名称将随附，因此您代码中的引用将保持有效。
回答by user3561813
One method is to search the header row for the Column name that you want to use. The returned Range object's column property indicates the column you'd want to use. See the below code and let us know if you need additional help.
一种方法是在标题行中搜索要使用的列名称。返回的 Range 对象的 column 属性指示您要使用的列。请参阅以下代码，如果您需要其他帮助，请告诉我们。
Sub LocateByHeader()
Dim rngFnder As Range
Dim rngHeader As Range
Const HEADER_ROW As Integer = 1
Set rngHeader = Intersect(Sheet1.Rows(HEADER_ROW), Sheet1.UsedRange)
Set rngFnder = rngHeader.Find("Customer")
If rngFnder Is Nothing Then
'Do some code in case the header isn't there
Else
' rngFnder.Column will give the index of the column with the customer name
End If
End Sub
