Excel VBA

Convert Excel Column Number to corresponding Column Letter, and Column Letter to corresponding Column Number in VBA

User Rating:  / 1
PoorBest 
Details

 

Convert Column Number to corresponding Column Letter, and Column Letter to corresponding Column Number in Excel VBA

 

Related Links: VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions.

 


 

 

VBA Codes to Convert Excel Column Number to corresponding Column Letter

 

 

Sub getColumnLetter1()
'convert excel column number to corresponding column letter using vba Chr function, viz. column no. 52 will convert to AZ
'code supports maximum of 4 letter columns ie. upto column letters "ZZZZ" which equates to column number 475254
'Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).

Dim colNumber As Long
Dim colLetter As String

colNumber = 25000
'enter column number for which you want the corresponding column letter

    
If colNumber < 1 Then
MsgBox "Out of Range"
Exit Sub
    
'for one letter column:
ElseIf colNumber < 27 Then
colLetter = Chr(colNumber + 64)
'first letter "A" has a column number of 65, hence the use of 64 (65 minus 1)

'for two letter column:
ElseIf colNumber < 703 Then
colLetter = Chr(Int((colNumber - 1) / 26) + 64) & Chr(((colNumber - 1) Mod 26) + 65)
'first letter "A" has a column number of 65, and one round of alphabets "A" to "Z" equals 26

'for three letter column:
ElseIf colNumber < 18279 Then
colLetter = Chr((Int((colNumber - 703) / 676) Mod 26) + 65) & Chr((Int((colNumber - 27) / 26) Mod 26) + 65) & Chr(Int((colNumber - 1) Mod 26) + 65)
'676 equates to 26*26; 702 equates to 26*26+26;

'for four letter column:
ElseIf colNumber < 475255 Then
colLetter = Chr((Int((colNumber - 18279) / 17576) Mod 26) + 65) & Chr((Int((colNumber - 703) / 676) Mod 26) + 65) & Chr((Int((colNumber - 27) / 26) Mod 26) + 65) & Chr(Int((colNumber - 1) Mod 26) + 65)
'17576 equates to 26*26*26; 18278 equates to 26*26*26+702;
    
Else
MsgBox "Out of Range"
Exit Sub
    
End If

    
MsgBox colLetter


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

Sub getColumnLetter2()
'convert excel column number to corresponding column letter, viz. column no. 52 will convert to AZ
'this code will work within the column limits of the excel workbook viz. excel 2003 column number limit of 256 (ie. column letters IV), and excel 2007 column limit of 16384 (column letters XFD).

Dim colNumber As Long
Dim colLetter As String

colNumber = 256
'enter column number for which you want the corresponding column letter

On Error GoTo ErrorHandler
'if column number is outside your excel range, the code will give an error


colLetter = Split(Cells(1, colNumber).Address, "$")(1)
'for column number 256 colLetter returns "IV", the first element of the array {IV;1} resulting from the Split function
'formula part "Cells(1, colNumber).Address" will return $IV$1, for column number 256

MsgBox colLetter
Exit Sub
'exit sub after returning column letter, before executing the ErrorHandler


ErrorHandler:
MsgBox "Column Number " & colNumber & " is Outside your Excel Version Range"
'error message if column number is outside your excel range

End Sub

 


 

 

VBA Codes to Convert Excel Column Letter to corresponding Column Number

 

 

Sub getColumnNumber1()
'convert excel column letter to corresponding column number using vba Chr function, viz. column letter AZ will convert to 52
'code supports maximum of 2 letter columns ie. upto column letters "ZZ" which equates to column number 702
'Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).

Dim colNumber As Integer
Dim colLetter As String

colLetter =  "zz"

'enter column letter in lower or upper case - lowercase will get converted to uppercase

 

colLetter = UCase(colLetter)

 

'for one letter column:

If Len(colLetter) = 1 Then

colNumber = Asc(colLetter) - 64

 

'for two letter column:

ElseIf Len(colLetter) = 2 Then

colNumber = (Asc(Left(colLetter, 1)) - 64) * 26 + (Asc(Right(colLetter, 1)) - 64)

 

Else

MsgBox "Out of Range"

Exit Sub

 

End If

 

MsgBox colNumber

 

End Sub

 

 

----------------------------------------------------------------------------------------------------------------------------------------------------------

 

Sub getColumnNumber2()
'convert excel column letter to corresponding column number using vba Chr function, viz. column letter AZ will convert to 52
'code supports any number of column letters
'Note: excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).

Dim colNumber As Long, colLetter As String, n As Integer

colLetter =  "c"

'enter column letter in lower or upper case - lowercase will get converted to uppercase

 

colLetter = UCase(colLetter)

 

colNumber = 0

For n = 1 To Len(colLetter)

colNumber = colNumber * 26 + (Asc(Mid(colLetter, n, 1)) - 64)

'one round of alphabets "A" to "Z" equals 26; formula part "colNumber * 26" will return zero for single column letter
Next

MsgBox colNumber


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

Sub getColumnNumber3()
'convert excel column letter to corresponding column number
'this code will work within the column limits of the excel workbook viz. excel 2003 column number limit is 256 (ie. column letters IV), while for excel 2007 it is 16384 (column letters XFD).

colLetter =  "AA" 

'enter column letter in lower or upper case

 

On Error GoTo ErrorHandler

'if column letter is outside your excel range, the code will give an error

 

colNumber = Range(colLetter & ":" & colLetter).Column
'for column letter "AA" , column number 27 is returned
'for column letter "AA" , formula part "colLetter & ":" & colLetter" returns "AA:AA"

MsgBox colNumber
Exit Sub


ErrorHandler:
MsgBox "Column Letter " & colLetter & " is Outside your Excel Version Range"
'error message if column letter is outside your excel range

End Sub

 

 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.