Public Function Lettre2NumCol(ByVal Chaine As String) As Long
Dim i As Long, ValeurCh As Long, v As Long
Const ChaineAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For i = 1 To Len(Chaine)
ValeurCh = InStr(1, ChaineAlpha, Mid(UCase(Chaine), i, 1))
v = v * 26 + ValeurCh
Next
Lettre2NumCol = v
End Function
Public Function NumCol2Lettre(ByVal NumCol As Long) As String
Dim i As Long, x As Long, s As String
For i = 6 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If NumCol > x Then
s = s & Chr(((NumCol - x - 1) \ 26 ^ i) Mod 26 + 65)
End If
Next i
NumCol2Lettre = s
End Function
Chez moi ça donne "A" pour chaque colonne de A à Z puis ça donne "A$", mais je me suis peut-être trompé.Bonsoir à tous, et malgré l'heure tardive, essayez :
=STXT(CELLULE("Adresse");2;1+(COLONNE()>26))
Bonne nuit
Wahou !! Splendide !!!Bonjour,
Une variante :
=SUBSTITUE(ADRESSE(1;COLONNE();4);1;"")
abcd
Function lettre(c)
lettre = Split(Cells(1, c).Address, "$")(1)
End Function
Function lettreb(num As Integer)
lettreb = Replace(Cells(1, num).Address(0, 0), "1", "")
End Function