asked on
How to convert Excel column numbers into alphabetical characters
In Access I write data into an Excel file and need to address cells. For example I want to address the 50 th column and need to get the column address (in this example AX).
I found a solution which not proper work: https://support.microsoft.com/de-de/kb/833402/en-us
53 results in A[ instead of BA .
Is there any proper solution?
I found a solution which not proper work: https://support.microsoft.com/de-de/kb/833402/en-us
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
53 results in A[ instead of BA .
Is there any proper solution?
Microsoft OfficeMicrosoft ExcelVBA
Last Comment
User2008
Of course Farwest answer is right
just to answer the question
just to answer the question
Function ColumnLetter(ColNumber As Long) As String
Dim lVar As Long
Dim bVar As Byte
Dim sVar As String
lVar = ColNumber
Do
bVar = ((lVar - 1) Mod 26)
sVar = Chr(bVar + 65) & sVar
lVar = (lVar - bVar) \ 26
Loop While lVar > 0
ColumnLetter = sVar
End Function
this is the implementation
Public Function GetColumnName(colindex As Integer) As String
GetColumnName = Split(Cells(1, colindex).Address, "$")(1)
End Function
Public Sub test()
Debug.Print GetColumnName(10)
End Sub
you can also use this single line of code if your column index will not exceed 701, that is not depend on worksheet reference or open sheet, and only if your column index will not exceed 701
Public Function GetColumnName(x As Integer) As String
GetColumnName = IIf(Int(x / 26) < 1, Chr(IIf(Int(x / 26) < 1, x, 1) + 64), Chr(Int(x / 26) + 64) + Chr((x - (Int(x / 26)) * 26) + 64))
End Function
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
good job @Rory
ASKER
It is working perfectly in Access
excamle
activesheet.cells(1,50) ' where 1 is row number and 50 is column number
also you use extract column part from this this
ActiveSheet.Cells(1, 50).Address