VBA实现列号与列名转换

自用

'列号转换为列名
Public Function NumToChr(PureNum As Integer) As String
    If PureNum Mod 26 = 0 Then
        NumToChr = VBA.IIf(PureNum \ 26 = 1, "", VBA.Chr(PureNum \ 26 + 63)) & "Z"
    Else
        NumToChr = VBA.IIf(PureNum \ 26 = 0, "", Chr(PureNum \ 26 + 64)) & Chr(PureNum Mod 26 + 64)
    End If
End Function
 

混混K提供

'列号转列标
Function Num2Col(ByVal colNum As Long) As String
Dim Str$, N&, I&, T&, Result$
Str = UCase("abcdefghijklmnopqrstuvwxyz")
T = Len(Str)
Do While colNum > T
    I = colNum Mod T
    If I = 0 Then
        I = T
        colNum = Int(colNum / T) - 1
    Else
        colNum = Int(colNum / T)
    End If
    Result = Mid(Str, I, 1) & Result
Loop
Result = Mid(Str, colNum, 1) & Result
Num2Col = Result
End Function

//【话唠】混混K®
'列标转列号
Function Col2Num(ByVal colStr As String) As Long
Dim N&, I&, T!
colStr = UCase(colStr)
T = Len(colStr)
For N = 1 To Len(colStr)
    I = I + (Asc(Mid(colStr, N, 1)) - 64) * (26 ^ (T - N))
Next N
Col2Num = I
End Function