Sometimes while manipulating data in Excel you’ll have a integer which represents the column number, but need the column letter.
The function below can translate that number to the column letter required. This function has been tested and works to 702, column ‘ZZ’.
Function ConvertToLetter(iCol As Integer) As String'-----------------------------------------------------------------------------
' Procedure : ConvertToLetter
' Author : Niall - Zypher.co.uk
' Purpose : Convert a number to a column letter
' Tested to 256 columns, assumed to work until 702 (ZZ)
'-----------------------------------------------------------------------------
'
If iCol <= 26 Then
' Columns A-Z
ConvertToLetter = Chr(iCol + 64)
Else
ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & _
Chr(((iCol - 1) Mod 26) + 65)
End If
End Function
As always, any questions or enquiries let us know via the comments section.
This comment has been removed by the author.
ReplyDeleteDoesn't work to ZZ, for example BA, is instead B[ or something like that, because the "chr" includes non-alphabets as well, any idea how to fix this?
ReplyDeletex = 1 ' or any number do you need :)
ReplyDeleteCells(1, x).Value = Split(Cells(1, x).Address, "$")(1)
Your website is really cool and this is a great inspiring article.
ReplyDeletevisual basic course london