Search and locate a column header using Excel VBA

You can use the Find method to do this. Example:

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 rngDateHeader instead of the hard-coded Range("J1").

I love Jean-Francois answer and built a function off of it. Hope it helps!

Update based on feedback from @Chris.Neilson:

First off, when I started VBA I used the syntax Range("Al:B1") to define a range and that is why my function returned a string. Id use something like this:

Dim TestHeaderColStr As String
TestHeaderColStr = FindHeaderCol(1,"test")
Dim Rng As Range
Set Rng = Range(TestHeaderColStr & "1:B1")

But, using numbers will save you headache and be much more readable like so:

Set Rng = Range(Cells(1,1),Cells(1,2))

So it would be better to have the function return a LONG (Integer can be to small for the number of rows/columns in new Excel). It would be easy to transform the below function to return an Long by changing As String to As Long and then change the Split line to be FindHeaderCol = rngHeaderCol.Column

Note: If you do leave As String you can always convert to number via Columns(FindHeaderCol(1,"test")).Column (Eg: debug.print Columns("G").Column)

I changed my function to take the values VBA uses and defined the default I prefer and the enumerations of the terms you will see such as xlNext.

Public Const xlNext As Integer = 1
Public Const xlPrevious As Integer = 2
Public Const xlWhole As Integer = 1
Public Const xlPart As Integer = 2
Public Const xlByRows As Integer = 1
Public Const xlByColumns As Integer = 2
Public Const xlComments As Long = -4144
Public Const xlCommentsThreaded As Long = -4184
Public Const xlFormulas As Long = -4123
Public Const xlValues As Long = -4163

Public Function FindHeaderCol(ByVal rngHeaderRowInt As Long, ByVal HeaderColStr As String, Optional ByVal WS As Worksheet, Optional ByVal LookIn As Long = xlValues, Optional ByVal LookAt As Integer = xlPart, Optional ByVal SearchOrder As Integer = xlByRows, Optional ByVal SearchDirection As Integer = xlNext, Optional ByVal MatchCase As Boolean = False, Optional ByVal MatchByte As Boolean = False, Optional ByVal SearchFormat As Boolean = False) As String
On Error GoTo ErrorHandler
 If WS Is Nothing Then Set WS = ActiveSheet
 Dim rngHeaderRow As Range, rngHeaderCol As Range
 Set rngHeaderRow = WS.Rows(rngHeaderRowInt).Cells
 'expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
 Set rngHeaderCol = rngHeaderRow.Find(What:=HeaderColStr, After:=rngHeaderRow.Cells(rngHeaderRow.Count), LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)
 If Not rngHeaderCol Is Nothing Then FindHeaderCol = Split(Cells(1, rngHeaderCol.Column).Address, "$")(1)
ErrorHandler:
End Function

Testing:

Public Sub Test_FindHeaderCol()
 Debug.Print "xlNext = " & FindHeaderCol(1, "TEST", Worksheets("Sheet1"), , , , xlNext)
 Debug.Print "xlPrevious = " & FindHeaderCol(1, "TEST", Worksheets("Sheet1"), , , , xlPrevious)
 Debug.Print "xlNext/xlWhole = " & FindHeaderCol(1, "TEST", Worksheets("Sheet1"), , xlWhole, , xlNext, True)
 Debug.Print "xlPrevious/xlWhole = " & FindHeaderCol(1, "TEST", Worksheets("Sheet1"), , xlWhole, , xlPrevious, True)
End Sub

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)

Sub Test()
 testcol = FindHeaderCol("1:1", "Test")
 MsgBox testcol
End Sub