Paste into a Comment

[jpstory]

I am trying to record a macro that copies numbers from one cell and paste into a comment, when recording, I copied the numbers from the formula bar and then pasted into the comment. However, every time when I run the macro, it repeatedly pastes the exactly same number I copied during recording the macro into the comment.

Any help will be appreciated , thank!

 

[Tom Urtis]

Not seeing your code, the crux is, you'd need to have the comment's text be that of the cell address' value. That is different than what VBA sees as a static act you are doing, which is copying a value from the formula bar. Refer to the value belonging to a cell, rather than the specific value itself. Example, comment.text = Range("A1").value [actually that is not real code butan attempt to relate the concept of what I am speaking about].

 

[RSXchin]

try this

With Range("a1")
    .Comment.Text (.Formula)
End With

 

[jpstory]

Thanks guys, greatly appreciate your help here.

Sorry for the confusion, I am trying to copy the numbers from any active cell and then paste the numbers into the comment on adjacent cell. Here is what I wrote:

Sub Copy_Paste_Comment()
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=
ActiveSheet.Paste
End Sub

The line in bold red is where I got stock. Any thoughts?

 

[Tom Urtis]

Can you define what "adjacent cell" means exactly for "any active cell". Example, if you have cell T743 selected, you would want the comment in cell U743 to contain the numbers or text or whatever that is in cell T743? And what if cell U743 does nor contain a comment? and what if it does, do you want T743's value to be added to, or be a replacement for, the current comment text?

 

[jpstory]

For example, the numbers will be in cell A1, and I will first create a comment in cell B1 which is empty, and then copy the numbers in cell A1 and paste into the comment in B1

 

[daverunt]

Try

Sub Copy_Paste_Comment()
ActiveCell.Offset(0, 1).AddComment
ActiveCell.Offset(0, 1).Comment.Visible = False
ActiveCell.Offset(0, 1).Comment.Text Text:=ActiveCell.Text
End Sub

 

[jpstory]

Try

Sub Copy_Paste_Comment()
ActiveCell.Offset(0, 1).AddComment
ActiveCell.Offset(0, 1).Comment.Visible = False
ActiveCell.Offset(0, 1).Comment.Text Text:=ActiveCell.Text
End Sub

Thanks daverunt, it worked out perfectly, thank you so much!!!!

 

[daverunt]

Great.

you could use a With ...

With ActiveCell.Offset(0, 1)
.AddComment
.Comment.Visible = True
.Comment.Text Text:=ActiveCell.Text
End With