Loop through range to extract Comments (New)

[tiredofit]

I came across this code to extract Comments (ie the New version):

https://www.mrexcel.com/board/threads/excel-vba-get-comment-from-cell.560025/

but it only worked for a single cell, D100 (which I know does indeed contains a Comment).


I adapted as follows by changing the range to C99:D101.

Dim varComment As String
Dim c As CommentThreaded

Dim rng As Range
Set rng = Sheet1.Range("C99:D101")

Dim rngelement As Range

For Each rngelement In rng.Cells
With rng
    On Error Resume Next
    Set c = .CommentThreaded
    On Error GoTo 0
    If c Is Nothing Then
        MsgBox "No comment", vbExclamation
    Else
        varComment = c.Text
        MsgBox varComment, vbInformation
    End If
End With
Next rngelement

but it failed to indicate cell D100 contained a Comment.

Can someone tell me what is wrong?

Thanks

 

[Fluff]

Your With is wrong, it's looking at the entire range, not the cell as you loop through.

 

[tiredofit]

Your With is wrong, it's looking at the entire range, not the cell as you loop through.

Thanks.

I changed it to:

Dim varComment As String
Dim c As CommentThreaded

Dim rng As Range
Set rng = Sheet1.Range("C99:D101")

Dim rngelement As Range

For Each rngelement In rng.Cells
With rngelement
    On Error Resume Next
    Set c = .CommentThreaded
    On Error GoTo 0
    If c Is Nothing Then
        MsgBox "No comment", vbExclamation
    Else
        varComment = c.Text
        MsgBox varComment, vbInformation
    End If
End With
Next rngelement

and it worked as expected.

 

[Fluff]

You're welcome & thanks for the feedback.