通过合并的单元格在VBA中的Excel工作簿上循环浏览注释 [英] Looping through comments on an excel workbook in vba with merged cells

问题描述

我有一本工作簿，里面有多张纸，上面有评论.我必须遍历每个工作表并选择评论.我已经实现了以下逻辑.

I have a workbook, with multiple sheets, which have comments. I have to loop through each of the sheets and pick up the comments. I have implemented the following logic.

For Each Ip_Sheet In ActiveWorkbook.Worksheets
    Set Rng = Ip_Sheet.Cells.SpecialCells(xlCellTypeComments)
    If Rng Is Nothing Then
        MsgBox "No comments in the sheet"
    Else
        For Each cell In Rng
            Comment_Author_NameAndComment = Split(cell.Comment.Text, ":")
            AuthName = Comment_Author_NameAndComment(0)
            AuthComments = Comment_Author_NameAndComment(1)

如果工作表中没有合并的单元格，则上述逻辑可以正常工作.但是，如果存在合并的单元格/行，则循环For Each cell In Rng为合并的单元格范围中的每个单元格运行.例如，如果列A:D被合并，则循环将针对单元格A，B，C和D运行，并且我在AuthName和AuthComments变量中获得相同的值.

The above logic works fine if there are no merged cells in the worksheet. However, if there are merged cells/rows, the loop For Each cell In Rng runs for each of the cells in the merged cells range. For example, if columns A:D are merged, then the loop runs for each of the cells A, B, C and D and I get the same value in the AuthName and AuthComments variables.

我的问题是，如果找到合并的单元格，如何使循环跳至工作表上的下一条注释?

My question is, how do I make the loop to skip to the next comment on the worksheet if I find a merged cell?

修改: 我还尝试通过以下方法遍历工作表中的所有注释，但是，该方法不成功-Rng.Comment对象始终为空.

I also tried to loop through all the comments in the sheet by the following method, however, the method was not successful - the Rng.Comment object was always empty.

        For Each cmnt_obj In Rng.Comment
            cmt_txt = cmnt_obj.Text
        Next cmnt_obj

推荐答案

由于SpecialCells(xlCellTypeComments)返回合并范围的所有单元格，因此您需要检测何时某个单元格是命名范围的一部分，并且仅处理其中一个单元格.您可以使用Range.MergeCells检测合并的单元格，并使用Range.MergeArea返回合并范围本身.然后，如果该单元格是合并范围的左上角单元格，则仅报告注释.

Since SpecialCells(xlCellTypeComments) returns all cells for a Merged Range, you need to detect when a cell is part of a named range and only process one of those cells. You can use Range.MergeCells to detect a merged cell, and Range.MergeArea to return the merged range itself. Then only report the comment if the cell is the Top Left cell of the merged range.

类似这样的东西:

Sub Demo()
    Dim rng As Range
    Dim cl As Range
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        Set rng = ws.Cells.SpecialCells(xlCellTypeComments)
        If Not rng Is Nothing Then
            For Each cl In rng.Cells
                If cl.MergeCells Then
                    If cl.Address = cl.MergeArea.Cells(1).Address Then
                        ReportComment cl
                    End If
                Else
                    ReportComment cl
                End If
            Next
        End If
    Next
End Sub

Sub ReportComment(cl As Range)
    Dim Comment_Author_NameAndComment() As String
    Dim AuthName As String
    Dim AuthComments As String

    Comment_Author_NameAndComment = Split(cl.Comment.Text, ":")
    AuthName = Comment_Author_NameAndComment(0)
    AuthComments = Comment_Author_NameAndComment(1)
    Debug.Print AuthName, AuthComments
    '...
End Sub

这篇关于通过合并的单元格在VBA中的Excel工作簿上循环浏览注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！