How to return a column's alpha value?

[RusheR]

I have this for a given cell.


n = ActiveCell.Column
i = ActiveCell.Row


n always returns a numeric vaule. How can I get it to return the columns alpha value (i.e. "C" instead of "3")

Thanks

 

[jasonb75]

Could you explain why you need the column returned as alpha?

It can be returned as a text string with Chr(n + 64), but allowances need to be made, that will only work up to column Z, it can still be done but depending on what you're going to do with the returned "C" next, there may be a more practical method.

 

[paul_taylor]

I needed this recently and found this code.


Function ColLetter(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
ColLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ColLetter = ColLetter & Chr(iRemainder + 64)
End If
End Function

 

[jasonb75]

Which is unreliable, for example, try =colletter(53) which should return "BA".

As I mentioned previously, it can be done this way, I have a version that will work beyond the number of columns in excel, but there is more than likely a more practical way to do this depending on the desired output.

 

[jasonb75]

This code could be used as Sub or Function, input column number as n output column lettter as nAlpha, works up to the current excel column limit, I didn't see any point in going beyond that

Dim n As Long, nAlpha As String
If n < 27 Then
    nAlpha = Chr(n + 64)
ElseIf n < 703 Then
    nAlpha = Chr(Int((n - 1) / 26) + 64) & Chr(((n - 1) Mod 26) + 65)
Else
    nAlpha = Chr(Int((n - 1) / (26 ^ 2)) + 64) & Chr(Int((n - 1) / 26) Mod 26 + 64) & Chr(((n - 1) Mod 26) + 65)
End If

 

[schielrn]

This is another way:

n = left(activecell.address(1, 0), instr(1, activecell.address(1, 0), "$") - 1)

Hope that helps.