AddComment does not work when using With Range("F15")-CodePudding

I'm trying to place some comments and values into a range of cells like this:

With Range("B5:C8")
   .AddComment "Current Sales"
End With

when I do that I get an error Run-time error "5": Invalid Procedure Call or Argument

When I try this:

With Range("B5:C8")
   .Value = 35
End With

it works fine, Is there something about the AddComment that works differently?

I'm using Excel 365 on Windows 10

Thanks for any help

CodePudding user response：

Loop through each cell ...

Dim objCell As Range

For Each objCell In Range("B5:C8")
   objCell.AddComment "Current Sales"
Next

... error checking ommitted.

CodePudding user response：

To handle a range with multiple cells, you will need a loop.

Also, since you cannot add more than one comment, you will need to append the new comment to the old comment.

Sub AddCellComment(FullRange As Range, cmt As String)
    Dim s As String
    Dim r As Range
    
    For Each r In FullRange
        If r.Comment Is Nothing Then
            r.AddComment cmt
        Else
            s = r.Comment.Text
            r.Comment.Delete
            ' append new comment
            r.AddComment s & vbCrLf & cmt
            ' OR replace original comment
            ' r.AddComment cmt
        End If
    Next
End Sub

Usage

Sub test()
    AddCellComment Range("B5:C8"), "test"
End Sub