CODEWITHSUNDEEP
vbaexcelcomments
Cooper
Cooper

Reputation: 21

Add Comments to Specific cells in Range. Excel VBA

I am trying to add comments to specific cells in a range if they meet criteria. So I have a list in Sheet1 where the information is housed. I also have the cell value on sheet16 where I want the comment in column U, so it will say F6 in row U. I keep getting

Application-Defined or object-defined error

Any thoughts?

Thanks in Advance.

Sub Comments()

    Dim rcell As Range

    Sheet16.Range("C6:AR17").ClearComments

    For Each rcell In Sheet1.Range("A2:A" & Sheet1.Range("A" & Sheet1.Rows.CountLarge).End(xlUp).Row)
        If rcell.Offset(0, 1).Value(10) = Sheet7.Range("G1").Value(10) Then
            commentvalue = rcell.Offset(0, 4).Value
            Sheet16.Range("U" & rcell.Row).AddComment (commentvalue)
        End If
    Next rcell

End Sub

Upvotes: 2

Views: 1237

Answers (1)

Excelosaurus
Excelosaurus

Reputation: 2849

The AddComment method fails if there's already a comment on a cell. Do it like this:

Sub Comments()
    Dim rcell As Range
    Dim commentvalue As String

    Sheet16.Range("C6:AR17").ClearComments

    For Each rcell In Sheet1.Range("A2:A" & Sheet1.Range("A" & Sheet1.Rows.CountLarge).End(xlUp).row)
        If rcell.Offset(0, 1).Value = Sheet7.Range("G1").Value Then
            commentvalue = CStr(rcell.Offset(0, 4).Value)

            With Sheet16.Range("U" & rcell.row)
                .ClearComments '<=== :-)
                .AddComment commentvalue
            End With
        End If
    Next rcell
End Sub

Edit

As per @Jeeped's comment, you could want to "cumulate" comments. My assumption was that you ran the code once, then ran it again and hit the error because the first run had created the comments. Depending on what you try to achieve, you may want to systematically clear the comments in column U from rows 2 to your last row, outside of the loop, and remove the .ClearComments in the loop, in order to start clean each time. That's the simplest case. I'll let you work out the details if it's anything more complicated.

Upvotes: 1

Related Questions