Long long time ago, when I was building some Excel VBA spreadsheet tool for a leading retailer, I was having some cheat-sheet in Excel, in order to find the number of the column in Excel – e.g. A-1, B-2, C-3 etc.
Today I was building something similar and I was really close to build that cheat sheet again, but I remembered that in my e-mail signature is written “Developer”, so building a cheat sheet for something like this should not be the case.
Thus, I managed to find in google the VBA function that I need to get the column name in Excel 🙂 After some time it worked and I managed to make the reverse function by myself. It all took me about 10 minutes, while building a cheat sheet would have taken 2. Anyway, it looks better this way. Here lies the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Function GetCol(ColumnNumber) As String Dim FuncRange As String Dim FuncColLength As Integer FuncRange = Cells(1, ColumnNumber).AddressLocal(False, False) FuncColLength = Len(FuncRange) GetCol = Left(FuncRange, FuncColLength - 1) End Function Function GiveCol(ColumnLetters) As String GiveCol = Range("A1:" & ColumnLetters & "1").Columns.Count End Function |
How the code works? If you are in trouble, you may write ?GiveCol(“B”) in the immediate window and you will get the result 2. For the first function, if you write GetCol(3), you will get C. As simple as that, if you know what you are looking for. Otherwise, have fun making cheat sheets and using them.
Note:
The GetCol function was found initially here:
http://www.mrexcel.com/forum/excel-questions/16444-getting-column-name-given-column-number.html
I simply changed some of the code, but the idea stays the same.