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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.