如何修改Excel VBA代码实现提取批注内容至单元格且保留无批注单元格的原有值

云上老周

2026-4-30

Fix: Keep Original Cell Values When No Comment Exists

Got it, let's sort out this VBA script for you. The issue with your original code is that it sets every cell's value to Rng.NoteText—and when a cell has no comment, this property returns an empty string, which wipes out the original cell content.

Modified Code

Here's the adjusted version that only replaces cells with existing comments, leaving others untouched:

Sub CommentToCell()
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    
    ' Loop through each cell in the selected range
    For Each Rng In WorkRng
        ' Only update the cell if it has a comment
        If Not Rng.Comment Is Nothing Then
            Rng.Value = Rng.Comment.Text ' Use .Text for modern Excel; .NoteText works too
        End If
    Next
End Sub

Key Changes Explained

Added a conditional check If Not Rng.Comment Is Nothing Then: This ensures we only modify cells that actually contain a comment.
Switched to Rng.Comment.Text (optional but recommended): While NoteText still works for backward compatibility, Comment.Text is the standard property for accessing comment content in newer Excel versions.
Left uncommented cells untouched: Since we skip the assignment for cells without comments, their original values stay intact.