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.

Report 1

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.

Report 2

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.

Solution :

Add these 2 functions to any module.

‘———————————————————————–

Option Explicit
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)
End Function

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)
End If
getColumn = i

ERRORHANDLER:
End Function

‘———————————————————————–

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)

Mysheet.Select

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

Next i

 

 

 

Advertisements