Note: This is for someone with little VBA knowledge.
How many times we have a need in VBA automation to find a column by name and not the number. This is especially useful if the report received keeps on changing the column number but the header remains the same.
|KT Project Name||Product Line||Account|
|Project Name 1||Pl1||account 1|
|Project Name 2||Pl2||account 2|
Say you encoded the VBA formula to run on column 3 like this
AccountName = ActiveSheet.Cells(2, 3)
Next time report is received the Column ‘Account’ changes from 3 to 2 the script will fail.
|KT Project Name||Account||Product Line|
|Project Name 1||account 1||Pl1|
|Project Name 2||account 2||Pl2|
Wouldn’t it be great if we can call by column name ‘Account’ and not 2.
Add these 2 functions to any module.
Option Base 1
Function getColFromAddr(addr As String) As Integer
‘This function returns “2” for “B” from input “$B$41”.
Dim iPos As Integer, thisAddr As String
thisAddr = addr
iPos = InStr(2, thisAddr, “$”)
thisAddr = Left(thisAddr, iPos – 1)
thisAddr = Right(thisAddr, Len(thisAddr) – 1)
getColFromAddr = getIntOfCol(thisAddr)
Function getColumn(mysheet As Worksheet, header As String, inRow As String) As Integer
‘ This function returns the index of the column of the first match for the header “header”
‘ in the sheet “mySheet” in the row “row”
Dim i As Integer, c As Range, addr As String, iPos As Integer
i = 0
On Error GoTo ERRORHANDLER
Set c = mysheet.Range(inRow & “:” & inRow).Find(header, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
addr = Right(c.Address, Len(c.Address) – 1)
‘iPos = InStr(addr, “$”)
‘addr = Left(addr, iPos – 1)
‘i = getIntOfCol(addr)
i = getColFromAddr(addr)
getColumn = i
Its always a good idea to set sheet as object and then call the function one in another
Dim MyBook As Workbook
Dim Mysheet As Worksheet
Set Mysheet = ActiveWorkbook.Worksheets(ActiveSheet.Name)
Set MyBook = Application.Workbooks(ActiveWorkbook.Name)
Account_Column = getColFromAddr(getAddress(Mysheet, “Account”))
‘ now we can use this anywhere in our code.
AccountName = MySheet.Cells(2,Account_Column )
I have a lot of these functions stored in a standard module that I just import when I need.Then I can use these functions all the time.
If there are comments, I shall post that Module for reference.
Bonus tip: When you need to loop to end of the sheet use this:
‘ Start from 2 as first row is usually header.
for i = 2 to MySheet.UsedRange.Rows.Count
‘ your loop there