Copy cell value to other cell Comment

[theojanissen]

Hi all,

Any help on this would make my day

I need a macro that places the result of the formula of e.g. cell B6 to the Comment box of e.g. cell D6. (Old values within the Comment box have to be removed first)

Thanks,

Theo

 

[KeepTrying]

Hi,

Try this code. If there is no formula in cell B6 then code will add empty comment to cell D6.

Sub FormulaToCellComment()


Dim Formula_ As String


Formula_ = Sheets("Sheet1").Range("b6").Formula


On Error Resume Next
Sheets("Sheet1").Range("d6").Comment.Delete
On Error GoTo 0


Sheets("Sheet1").Range("d6").AddComment Formula_


End Sub

Hope that it'll work for you.

Regards,

 

[theojanissen]

Hi,

Try this code. If there is no formula in cell B6 then code will add empty comment to cell D6.

Sub FormulaToCellComment()


Dim Formula_ As String


Formula_ = Sheets("Sheet1").Range("b6").Formula


On Error Resume Next
Sheets("Sheet1").Range("d6").Comment.Delete
On Error GoTo 0


Sheets("Sheet1").Range("d6").AddComment Formula_


End Sub

Hope that it'll work for you.

Regards,

Thanks.

In the mean time I found a waythat works also:

Sub CellValueToCellCcontent()
Range("B6").Select
y = ActiveCell.Value
Range("D6").Select
ActiveCell.Comment.Text y
End Sub

 

[KeepTrying]

I tested your code and it didn't work for me. It stopped at the last row. If I update last row in your code like this, it works:

Sub CellValueToCellCcontent()


Dim Y


Range("B6").Select
Y = ActiveCell.Value
Range("D6").Select
'ActiveCell.Comment.Text Y


ActiveCell.AddComment CStr(Y)


End Sub

However it will work only if there is no comment in cell D6. I'd suggest to use my code. I updated .formula to .value:

Sub FormulaToCellComment()


Dim Formula_ As String


Formula_ = Sheets("Sheet1").Range("b6").Value 'I updated this row


On Error Resume Next
Sheets("Sheet1").Range("d6").Comment.Delete
On Error GoTo 0


Sheets("Sheet1").Range("d6").AddComment Formula_


End Sub