vba 按列名引用单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14524053/
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
Reference a cell by its column name
提问by SillyCoda
I want to update the contents of a cell in a workbook. My code looks a little like this:
我想更新工作簿中单元格的内容。我的代码看起来有点像这样：
ProductionWorkBook.Sheets("Production Schedule").Cells(StartRow, 1).Value = EstJobName(i)
The cells are referenced using Cells(StartRow, 1)Where StartRow was a pre-declared and pre-defined integer variable that specifies the row and "1" denotes the column.
使用Cells(StartRow, 1)StartRow 是一个预先声明和预先定义的整数变量来引用单元格，该变量指定行，“ 1”表示列。
EDIT: Now, I want to change this code to reference the columns by the column HEADERSinstead.
编辑：现在，我想更改此代码以通过列 HEADERS引用列。
For example, the header of a column is: "Fab Hours Date", how do I reference that?
例如，一列的标题是：“Fab Hours Date”，我该如何引用？
回答by Vicky
Yes, you can simply use the letter name for the column in quotes:
是的，您可以简单地在引号中使用列的字母名称：
Cells(StartRow, "A")
Edited to answer your further question: to look for a specific column name, try this:
编辑以回答您的进一步问题：要查找特定的列名称，请尝试以下操作：
columnNamesRow = 1 ' or whichever row your names are in
nameToSearch = "Fab Hours" ' or whatever name you want to search for
columnToUse = 0
lastUsedColumn = Worksheets("Foo").Cells(1, Worksheets("Foo").Columns.Count).End(xlToLeft).Column
For col = 1 To lastUsedColumn
If Worksheets("Foo").Cells(columnNamesRow, col).Value = nameToSearch Then
columnToUse = col
End If
Next col
If columnToUse > 0 Then
' found the column you wanted, do your thing here using "columnToUse" as the column index
End If
回答by Daniel
Here are two different functions to get what you want. To use them, you'd have to put them in your code.
这里有两个不同的函数来获得你想要的。要使用它们，您必须将它们放入您的代码中。
Function ColumnNumberByHeader(text As String, Optional headerRange As Range) As Long
Dim foundRange As Range
If (headerRange Is Nothing) Then
Set headerRange = Range("1:1")
End If
Set foundRange = headerRange.Find(text)
If (foundRange Is Nothing) Then
MsgBox "Could not find column that matches header: " & text, vbCritical, "Header Not Found"
ColumnNumberByHeader = 0
Else
ColumnNumberByHeader = foundRange.Column
End If
End Function
Function ColumnNumberByHeader2(text As String, Optional headerRange As Range) As Long
If (headerRange Is Nothing) Then
Set headerRange = Range("1:1")
End If
On Error Resume Next
ColumnNumberByHeader2 = WorksheetFunction.Match(text, headerRange, False)
If Err.Number <> 0 Then
MsgBox "Could not find column that matches header: " & text, vbCritical, "Header Not Found"
ColumnNumberByHeader2 = 0
End If
On Error GoTo 0
End Function
Example Calls:
示例调用：
ColumnNumberByHeader ("Extn")
ColumnNumberByHeader("1718", Range("2:2"))
Or in your case:
或者在你的情况下：
ProductionWorkBook.Sheets("Production Schedule"). _
Cells(StartRow, ColumnNumberByHeader("Fab Hours Date")).Value = EstJobName(i)
回答by Dan
ProductionWorkBook.Sheets("Production Schedule").Range("A" & StartRow).Value = EstJobName(i)
Unless you mean the column is a named range you defined?
除非您的意思是该列是您定义的命名范围？
回答by Peter Albert
ProductionWorkBook.Sheets("Production Schedule").Range("E"& StartRow).Value = ...
will do the job.
会做的工作。
Though keep in mind that using hard coded references like the column letter will risk that the macro breaks when the sheet is edited (e.g. a column is inserted). It's therefore better to use a named range and Offsetto access:
但请记住，使用像列字母这样的硬编码引用会在编辑工作表（例如插入列）时存在宏中断的风险。因此最好使用命名范围并Offset访问：
ProductionWorkBook.Sheets("Production Schedule").Range("StartCell").Offset(StartRow-1).Value
Now you only need to provide the name StartCellto your fist cell (make sure that it's a local name in the Name Manager)
现在您只需要为StartCell您的第一个单元格提供名称（确保它是名称管理器中的本地名称）
