Convert Column letter designation to a number

[Fred Holmes]

Is there a code statement that will convert the letter designation of
a column to a number, e.g., convert "A" to "1" and and so forth using
the letter pattern used to designate columns in a worksheed?

"AA" would be converted to "27".

I need to do arithmetic using the column numbers.

Thanks,

Fred Holmes

 

[Steve]

http://www.vba-programmer.com/Snippets/Code_Excel/Convert_ColLtr_To_Num.html

 

[Jim Thomlinson]

Give this a whirl...

Sub test()
MsgBox LetterToNumber("A")
MsgBox LetterToNumber("r")
MsgBox LetterToNumber("FA")
End Sub

Public Function LetterToNumber(ByVal Letter As String) As Long
Dim lngLength As Long
Dim lng As Long
Dim lngReturn As Long

lngLength = Len(Letter)
For lng = 1 To lngLength
lngReturn = lngReturn + (Asc(UCase(Mid(Letter, lng, 1))) - 64) _
* ((lngLength - lng) * 25 + 1)
Next lng
LetterToNumber = lngReturn
End Function

 

[Dave Peterson]

You can get the column number by:

msgbox cells(1,"AA").column
or
msgbox range("AA"&1).column

If you have a couple of range variables, you could use:

dim myCell_1 as range
dim myCell_2 as range

'set them to something

msgbox mycell_2.column - mycell_1.column
(to see the difference)

 

[Gord Dibben]

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=getcolnum("AA") - getcolnum("M") returns 14


Gord Dibben MS Excel MVP

 

[Dave Peterson]

This
msgbox range("AA"&1).column

should be
msgbox range("AA1").column
or
msgbox range("AA" & "1").column

 

[Gary Keramidas]

why not just:

columns("AA").column