修复合并单元格内容转批注/注释的VBA代码报错（错误400）

Hey there, let's tackle that error 400 you're getting when running your VBA code on merged cells. The core issue is that when you select merged cells, your original loop tries to process every single cell in the selection—including the "empty" cells in the merged area that don't actually hold the value. Merged cells are linked, so only the top-left cell in the merged range stores the content, and modifying other cells in the area triggers errors.

Here's the fixed code that handles merged cells properly:

Sub ConvertToComment()
    Dim C As Range
    
    For Each C In Selection
        ' Only process the top-left cell of a merged area, or non-merged cells
        If (C.MergeCells And C.Address = C.MergeArea.Cells(1).Address) Or Not C.MergeCells Then
            ' Clear existing comment associated with the cell/merged area
            C.ClearComments
            
            ' Check if there's content to convert to a comment
            If Len(C.Value) > 0 Then
                ' Add comment to the top-left cell (controls the merged area's comment)
                C.AddComment
                C.Comment.Text Text:=C.Value
            End If
            
            ' Optional: Clear content from the entire merged area (or just the single cell if non-merged)
            C.MergeArea.ClearContents
        End If
    Next C
End Sub

Key Fixes Explained: #

• Merged Cell Targeting: The condition (C.MergeCells And C.Address = C.MergeArea.Cells(1).Address) Or Not C.MergeCells ensures we only work with the top-left cell of any merged range, plus all non-merged cells. This avoids wasting time (and triggering errors) on cells that don't hold the merged content.
• Whole Area Content Clear: Using C.MergeArea.ClearContents instead of C.ClearContents makes sure the entire merged cell range is emptied, not just the single cell we're processing.
• Safe Comment Management: Comments on merged cells are attached to the top-left cell, so we only add/clear comments there to prevent conflicts and redundant actions.

Just select your target range (including merged cells) and run this macro—it'll convert each merged area's content (or single cell's content) to a comment, then clear the cell content if you keep the optional line enabled.

内容的提问来源于stack exchange，提问作者Aleksei Kovaltsuk