xxxxxxxxxx
Sub Test()
Debug.Print "result: " & getColIndex("DD") ' --> result: 108
End Sub
Function getColIndex(sColRef As String) As Long
Dim i As Long, sum As Long, iRefLen As Long
sum = 0: iRefLen = Len(sColRef)
For i = iRefLen To 1 Step -1
sum = sum + Base26(Mid(sColRef, i)) * 26 ^ (iRefLen - i)
Next
getColIndex = sum
End Function
Private Function Base26(sLetter As String) As Long
Base26 = Asc(UCase(sLetter)) - 64 'fixed
End Function
xxxxxxxxxx
Function ColName(n)
ColName = Split(Cells(, n).Address, "$")(1)
End Function
xxxxxxxxxx
Sub Letter2Number()
'PURPOSE: Convert a given letter into it's corresponding Numeric Reference
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim ColumnNumber As Long
Dim ColumnLetter As String
'Input Column Letter
ColumnLetter = "AG"
'Convert To Column Number
ColumnNumber = Range(ColumnLetter & 1).Column
'Display Result
MsgBox "Column " & ColumnLetter & " = Column " & ColumnNumber
End Sub