vba excel - For Each cell In range - read comments

user_7894473 0
Viewed 48

there. I'm trying to retrieve comments from a pre-defined name range of cells, but I keep getting the error message for the instruction: Set c = Range("myRange").Comment.Value

Only the active workbook is open, range is there, etc. In the meantime, a value has been assigned to c, that corresponds to very first cell of myRange.

Any ideas? thanks a lot.

error message

value returned to c

Sub Test_findComments_in_range()
Dim varComment As String
Dim c As Variant

With ActiveSheet
    For Each c In .Range("myRange")
    
    Set c = Range("myRange").Comment.Value
        
        If IsEmpty(c) Then
        
        MsgBox "No comment found! "
            
         GoTo jumpCycle3

        Else
            varComment = c.Value
            MsgBox varComment, vbInformation
            
            
        End If
    
    

jumpCycle3:    Next

End With

End Sub
range comments vba excel loops
2 Answers

There is no need to go through all the cells in the range and check each one for a comment, you can use the Range.SpecialCells method with the parameter xlCellTypeComments

Sub ListComments()
    Dim rng As Range
    
    Set rng = ActiveSheet.Range("myRange")
    On Error GoTo out ' handle the error that occurs if there is no cell with comments
    Set rng = rng.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
    
    For Each cl In rng ' iterate over all cells in rng - each of them contains a comment
        MsgBox cl.Address & " : " & cl.Comment.Text, vbInformation
    Next
    Exit Sub

out:        MsgBox "No comments in the area"
End Sub
user_150353140

Find Comments in a Named Range

Option Explicit

Sub FindCommentsInNamedRange()

    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the named range ('rg').
    Dim rg As Range: Set rg = wb.Names("myRange").RefersToRange

    ' To reference the range's worksheet (not necessary here), you could use:
    'Dim ws As Worksheet: Set ws = rg.Worksheet
    
    Dim cell As Range
    Dim cm As Comment
    Dim cText As String
    
    ' Loop through the cells of the range (note 'rg.Cells')...
    For Each cell In rg.Cells
        ' Attempt to reference the cell's comment ('cm').
        Set cm = cell.Comment
        ' Validate the comment.
        If cm Is Nothing Then
            MsgBox "No comment found!", vbExclamation, _
                "Comment in Cell '" & cell.Address(0, 0) & "'"
        Else
            ' Write the comment's text to a variable ('cText').
            cText = cm.Text
            MsgBox cText, vbInformation, _
                "Comment in Cell '" & cell.Address(0, 0) & "'"
        End If
    Next cell

End Sub
user_98140690
Related