使用 Excel VBA 搜索和定位列标题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28804402/
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
Search and locate a column header using Excel VBA
提问by
I have a worksheet where the column header positions change regurlary.
我有一个工作表，其中列标题的位置会发生变化。
For example if today "Date" is in column J1 as the column header, tomorrow "Date" could be in column C1. Therefore if I want to perform sorting operations, using a macro for date, I cannot refer to it using a hard-coded cell reference for example
例如，如果今天“日期”在列 J1 中作为列标题，那么明天“日期”可能在列 C1 中。因此，如果我想执行排序操作，使用日期宏，我不能使用硬编码的单元格引用来引用它，例如
cu.Range("J1").AutoFilter Field:=10, Criteria1:=">=" & Date
Is there a way to search and locate a column header "Date". So that after I locate, I can perform the sorting based on current date?
有没有办法搜索和定位列标题“日期”。以便在我定位后，我可以根据当前日期执行排序？
回答by Jean-Fran?ois Corbett
You can use the Findmethod to do this. Example:
您可以使用该Find方法来执行此操作。例子：
Dim rngDateHeader As Range
Dim rngHeaders As Range
Set rngHeaders = Range("1:1") 'Looks in entire first row; adjust as needed.
Set rngDateHeader = rngHeaders.Find("Date")
You would then use rngDateHeaderinstead of the hard-coded Range("J1").
然后，您将使用rngDateHeader而不是硬编码的Range("J1").
回答by FreeSoftwareServers
I love Jean-Francois answerand built a function off of it. Hope it helps!
我喜欢让-弗朗索瓦的回答，并以此为基础构建了一个功能。希望能帮助到你！
Function:
功能：
Function FindHeaderCol(rngHeaderRow, rngHeaderCol) As String
Set rngHeaderRow = Range(rngHeaderRow)
Set rngHeaderCol = rngHeaderRow.Find(rngHeaderCol)
FindHeaderCol = Split(Cells(1, rngHeaderCol.Column).Address, "$")(1)
End Function
Testing:(Note: Enter the word "Test" as a column header anywhere in row 1)
测试：（注意：在第 1 行的任何位置输入“测试”一词作为列标题）
Sub Test()
testcol = FindHeaderCol("1:1", "Test")
MsgBox testcol
End Sub
