Excel 用 vba 批量插入/提取批注

笑虾

已于 2022-06-16 18:46:26 修改

阅读量5.1k

点赞数 1

分类专栏： Office+WPS # VBA 文章标签： excel vba 批注

于 2020-06-11 22:11:50 首次发布

大家好，我是笨笨，笨笨的笨，笨笨的笨，转载请注明出处，谢谢！

本文链接：https://blog.csdn.net/jx520/article/details/106698136

版权

Office+WPS 同时被 2 个专栏收录

34 篇文章

订阅专栏

VBA

21 篇文章

订阅专栏

批量插入批注

Sub 批量插入批注()
On Error Resume Next

    '批注来源列名
    Dim targetColumnName As String
    '批注来源列号
    Dim offsetColumn As Integer
    '计数
    Dim count As Integer
    
    targetColumnName = InputBox("直接输入列名字母： ", "批注来源列")
    If targetColumnName = "" Then
        GoTo gotoEnd
    End If
    
    '算出目标列号
    offsetColumn = Range("A1:" & targetColumnName & "1").Cells.count
    
    '遍历所选列，从目标列对应行中取值插入为批注
    For Each sel In Selection
        With sel
            If Not Cells(sel.Row, offsetColumn) = "" Then
                .ClearComments
                .AddComment
                .Comment.Text Text:=Cells(sel.Row, offsetColumn).Value
                .Comment.Visible = False
                count = count + 1
            End If
        End With
    Next
    
    MsgBox "批量插入批注 " & count & "个", vbOKOnly, "批量批注完成"
    
gotoEnd:

End Sub

批量提取批注

Sub 批量提取批注()
On Error Resume Next

    '批注存放列名
    Dim targetColumnName As String
    '向左偏移几列取值
    Dim offsetColumn As Integer
    '计数
    Dim count As Integer
    
    targetColumnName = InputBox("直接输入列名字母： ", "批注内容接受列")
    If targetColumnName = "" Then
        GoTo gotoEnd
    End If
    
    '算出目标列号
    offsetColumn = Range("A1:" & targetColumnName & "1").Cells.count
    
    
    '遍历所选列，从目标列对应行中取值插入为批注
    For Each sel In Selection
        With sel
            If Not .Comment.Text = "" Then
                Cells(sel.Row, offsetColumn).Value = .Comment.Text
                .ClearComments
                count = count + 1
            End If
        End With
    Next
    
    MsgBox "批量提取批注 " & count & "个", vbOKOnly, "批量提取完成"
    
gotoEnd:

End Sub