Excel VBA Get Column Index
Over the last few months I’ve been doing more work in excel than is good for anyone’s health, trying to create a database best suited for access. Still, in the real world, you have to work on the tools available.
The biggest problem I found was getting column indexes for the relevant information. The quickest method is just to hard code the indexes inside you code A1 B1 etc… However this doesn’t help if you end up needing to add a new column cause you’ll find your self having to search your code for ever occurrence of B1 and changing it to D1 and so on.
So the solution I came up with is a function to search and excel worksheet for a column name and return the correct letters. Now seeing as you probably need to run the same search multiple times its a performance boost to cache these results.
The code included here does just that. All you need to do is define ‘columnIndexRefernce’ globally and set it as a new collection outwith this function, but once thats been done you can call fndDataSheetColumn any time you want the correct column.
Dim columnIndexRefernce As Collection columnIndexRefernce = New Collection Private Function fndDataSheetColumn( wrkSheet as String, columnName as String, headerRow as Integer) As String Dim var As Variant Dim errNumber As Long Dim colABC As String set var = Nothing Err.Clear On Error Resume Next var = columnIndexRefernce.Item(columnName) errNumber = CLng(Err.Number) On Error GoTo 0 If errNumber = 5 Then Dim wSht As Worksheet set wSht = Worksheet(wrkSheet) With wSht Dim Found As Range Set Found = .Rows(headerRow).Find(what:=columnName, LookIn:=xlvalues, lookat:=xlwhole) If Not Found Is Nothing Then colABC = ColLtr(Found.Column) columnIndexRefernce.Add colABC, columnName fndDataSheetColumn = columnIndexRefernce(columnName) End If End With Else fndDataSheetColumn = var End If End Function Function ColLtr(ByVal iCol As Long) As String If iCol then ColLtr = ColLtr((iCol -1) \ 26) & Chr(65 + (iCol -1) Mod 26) End Function