Reference to header instead of hardcoding column letter?

[doctortt]

Hi all, Instead of hardcoding "G", "E", and "F", is there a way to make the VBA codes to use the headers? For example, The header for G is apple, E is orange, and F is banana. [ CODE ] Dim iFor i = 2 To Cells(Rows.Count, "A").End(xlUp).Row Cells(i, "G").Value = Cells(i, "E").Value & " " & Cells(i, "F").ValueNext i[ /CODE ]

[Paul_Hossler]

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