Usage a cell's content to add comment for a different cell-CodePudding

So my problem here is the following: I would like to use a cell's value which comes from a formula. I'll try to explain the best way:

A1's value: x (here I need a comment) B1's value: yz (the text here comes from a vlookup formula)

I would like to have B1's value in A1 cell as a comment.

Here is what I have, if you have a suggestion please let me know. Thank you in advance!

Sub commenter()

Dim text As String
text = Worksheets(1).Range("B1").Value

Worksheets(1).Range("A1").AddComment text

End Sub

CodePudding user response：

The code you have should work - however only if the cell doesn't have a comment. If it has already a comment, you will get a runtime error 1004.

As the text-property of a comment is read-only, you cannot modify the text, so best is to simply delete it before you set it:

With ThisWorkbook.Sheets(1)
    Dim text As String, cell As Range
    text = .Range("B1").Value
    Set cell = .Range("A1")
    If Not cell.Comment Is Nothing Then cell.Comment.Delete
    cell.AddComment text
End With