Log in

View Full Version : Reference to header instead of hardcoding column letter?



doctortt
11-26-2013, 08:12 AM
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
11-26-2013, 08:41 AM
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