excel用VBA插入列号

最新推荐文章于 2023-10-05 21:19:07 发布

月清晖

最新推荐文章于 2023-10-05 21:19:07 发布

阅读量1.8k

点赞数

分类专栏：办公技巧文章标签： vba

本文链接：https://blog.csdn.net/impish0/article/details/122088453

版权

有时候为了做数据透视，但是列很多，看起来眼花，不如插入列号来的方便

比如第一列“序号”变成“A__序号”,27列变为AA开头，依次类推。

Private Sub 插入列号()
i = 1
Do Until Cells(1, i) = ""
Cells(1, i) = Fun_GetEngName(Cells(1, i).Column) & "__" & Cells(1, i)
i = i + 1
Loop
End Sub

Public Function Fun_GetEngName(ByVal argColumn As Integer) As String
    Dim strEngName As String
    Dim iNum, iMod As Integer

    iNum = argColumn \ 26
    iMod = argColumn Mod 26
    If (iMod = 0) Then
        If (iNum = 1) Then
            strEngName = Chr(90)
        Else
            strEngName = Chr(65 + iNum - 2) + Chr(90)
        End If
    Else
        If (iNum = 0) Then
            strEngName = Chr(65 + iMod - 1)
        Else
            strEngName = Chr(65 + iNum - 1) + Chr(65 + iMod - 1)
        End If
    End If
    Fun_GetEngName = strEngName
End Function

对应的，做了还原，“A__