Fonction pour convertir un numéro de colonne en lettre ?

Question

Quelqu'un dispose-t-il d'une fonction Excel VBA permettant de retourner la ou les lettres de la colonne d'un nombre ?

Par exemple, en saisissant 100 devrait retourner CV .

Answer 1

Cette fonction renvoie la lettre de la colonne pour un numéro de colonne donné.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

code d'essai pour la colonne 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

Answer 2

Si vous préférez ne pas utiliser un objet de gamme :

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

Answer 3

Un truc qui marche pour moi est :

Cells(Row,Column).Address 

Cela vous renverra la référence du format $AE$1.

Answer 4

• Par exemple : MsgBox Columns( 9347 ).Address renvoie à _{<img src="https://i.stack.imgur.com/A2y11.png" alt="<code>$MUM:$MUM</code>">} .

---

Pour revenir UNIQUEMENT la lettre de la colonne (s) : Split((Columns(Column Index).Address(,0)),":")(0)

• Par exemple : MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) renvoie à _{<img src="https://i.stack.imgur.com/e0c5y.png" alt="<code>DAD</code>">} .

---

  ![More Examples](https://i.stack.imgur.com/7lg2r.gif "SURE, I may have a bit too much time on my hands...

...or maybe I saw an chance to test to see how smooth/ noticeable ScreenToGif's image fading transitions are.

After all, Stack Overflow is a place for learning, right? (and it is programming-related so I am technically on-topic!) You probably didn't even know you could add tool-tips to images in Stack Overflow posts, did ya?

You're the one that was staring at the animation long enough to notice this tool-tip - and now, look, you're still sitting here, reading it all!

Now c'mon, how 'bout a 'pity up-vote'? Ya never know, it might just earn you some valuable Karma Points! :)")

---

Answer 5

Et une solution utilisant la récursion :

Function ColumnNumberToLetter(iCol As Long) As String

    Dim lAlpha As Long
    Dim lRemainder As Long

    If iCol <= 26 Then
        ColumnNumberToLetter = Chr(iCol + 64)
    Else
        lRemainder = iCol Mod 26
        lAlpha = Int(iCol / 26)
        If lRemainder = 0 Then
            lRemainder = 26
            lAlpha = lAlpha - 1
        End If
        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
    End If

End Function