## Sunday, May 27, 2012

### Excel and Excel VBA - Convert column letter to number

The columns of the excel sheet are labeled as ‘A’, ‘B,’ ‘C’…. It looks straightforward but it gives the difficult when I did VBA programming.

What is the corresponding number to the column K?

1) Count by figure.

2) In Cell A1, type in =1
In Cell B1, type in  “=A1+1”
Then, drag the formula from Cell B1 to Cell K1

3) In Cell A1, type in  =CODE("K")-64

The second question, what is the corresponding number to the column BK?

1) Count by figure. (I do not think it is a good idea)

2) In Cell A1, type in =1
In Cell B1, type in  “=A1+1”
Then, drag the formula from Cell B1 to Cell BK1 (It is not a good idea neither)

3) In Cell A1, type in  =(CODE("B")-64 )*26+(CODE("K")-64 )

Therefore, you can see the method 1 and 2 are not bad for the first couple of excel columns but it is not a good idea when number of columns is increased.

Method 3 is a good idea.

The method 4 is using VBA code. I prefer this.

Sub ColumnLetterToNumber()

Dim InputLetter As String
Dim OutputNumber As Integer
Dim Leng As Integer
Dim i As Integer

InputLetter = InputBox("The Converting letter?")  ' Input the Column Letter
Leng = Len(InputLetter)
OutputNumber = 0

For i = 1 To Leng
OutputNumber = (Asc(UCase(Mid(InputLetter, i, 1))) - 64) + OutputNumber * 26
Next i

MsgBox OutputNumber   'Output the corresponding number

End Sub

Using the above code, I can quickly find the number I need. And both lower case and upper case letter give the same result.

For example,

Input letter = “abc”

Output number = 731

1. If you are going to use the VBA Editor then this would be quicker and easier:
MsgBox ActiveCell.Column

2. For those who are regularly programming VBA code, you can use the Immediate window located at the bottom of the VBA Editor by typing this:
?ActiveCell.Column

Once you type this string, press Enter. The Immediate window will return the corresponding number for your Column.

It will look like this:
?ActiveCell.Column
731

3. colNumber = Range(colLetters & "1").Column

4. This comment has been removed by the author.