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**

**D**

**im 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

© Copyright Exceltipsandkeys All Rights Reserved.

If you are going to use the VBA Editor then this would be quicker and easier:

ReplyDeleteMsgBox ActiveCell.Column

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:

ReplyDelete?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

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

ReplyDeleteThis comment has been removed by the author.

ReplyDelete