The best way I've found for a general purpose method is to have a simple function return the column number. You could have it return the range also, but I think the number is more flexible

Option Explicit
Function ColNum(ColHeader As String, Optional WS As String = vbNullString) As Long
    Dim wsHeaders As Worksheet
    
    If Len(WS) = 0 Then
        Set wsHeaders = ActiveSheet
    Else
        Set wsHeaders = Worksheets(WS)
    End If
    
    ColNum = 0
    On Error Resume Next
    ColNum = Application.WorksheetFunction.Match(ColHeader, wsHeaders.Rows(1), 0)
    On Error GoTo 0
End Function


 
Function ColNumR(ColHeader As String, Optional WS As String = vbNullString) As Range
    Dim wsHeaders As Worksheet
    
    If Len(WS) = 0 Then
        Set wsHeaders = ActiveSheet
    Else
        Set wsHeaders = Worksheets(WS)
    End If
    
    Set ColNumR = Nothing
    On Error Resume Next
    Set ColNumR = wsHeaders.Columns(Application.WorksheetFunction.Match(ColHeader, wsHeaders.Rows(1), 0))
    On Error GoTo 0
End Function

 


Sub test()
    Dim r As Range
    
'    MsgBox ColNum("AAA")            ' 1
'    MsgBox ColNum("AAA", "Sheet1")              '1
'    MsgBox ColNum("NOT THERE")              '0
    Set r = ActiveSheet.Columns(ColNum("CCC"))
    MsgBox r.Address                       ' $C:$C
End Sub

Paul