Solved: Copy Range and paste it into a insert comment

[Shazam]

I need some help. I would like to copy a range of cells and paste it into a insert comment.

Example: I would like the code to copy a range of cells of A2:A9 from worksheet "Range" and paste it into a insert comment in cell A2 of worksheet "Expected Results"

I have this code below but it will not work in a range of cells.

[VBA]
Public Sub test()
Dim x As String
Dim w As Worksheet

w="Range"
x = Range("A2:A9").Value
Range("A2").AddComment x
End Sub
[/VBA]



I left a example workbook below.

[rory]

Try this:
[VBA]Sub AddComments2()
'Posted by Dave Ramage, 2001-04-11, misc,
Dim rngComments As Range, rngCells As Range
Dim varComments
Dim strComment As String

'get user to select range
Set rngComments = Application.InputBox(prompt:="Select" _
& " range containing comments text:", _
Title:="Add comments: Step 1 of 2", Type:=8)
'was Cancel pressed?
If rngComments Is Nothing Then Exit Sub

Set rngCells = Application.InputBox(prompt:="Select a cell to add comment to:", _
Title:="Add comments: Step 2 of 2", _
Type:=8)
If rngCells Is Nothing Then Exit Sub
varComments = rngComments.Value
'are ranges the same size?
Set rngCells = rngCells.Cells(1)
strComment = Join(Application.Transpose(varComments), vbLf)
'add comments
'does the cell already have a comment? If so, delete it
If Not rngCells.Comment Is Nothing Then rngCellsComment.Delete
' Add new comment
rngCells.AddComment strComment

End Sub
[/VBA]

Regards,
Rory

[Bob Phillips]

One simple way

[vba]

Sub Test()
Dim mpCell As Range
Dim mpTemp As String

For Each mpCell In Range("A2:A9")
mpTemp = mpTemp & mpCell.Text & vbLf
Next mpCell
With Range("A2")
On Error Resume Next
.Comment.Delete
On Error Goto 0
.AddComment mpTemp
End With
End Sub
[/vba]

[Shazam]

Hi rory,

Thank you very much for the code.

Can it be modified without the inputbox? So the code could have the ranges hard coded in.

[Shazam]

One simple way

[vba]

Sub Test()
Dim mpCell As Range
Dim mpTemp As String

For Each mpCell In Range("A2:A9")
mpTemp = mpTemp & mpCell.Text & vbLf
Next mpCell
With Range("A2")
On Error Resume Next
.Comment.Delete
On Error Goto 0
.AddComment mpTemp
End With
End Sub
[/vba]

Thank you for replying xld. I ran your code and the insert comment comes out blank. Is the code point to the right worksheet tab? Or am I doing something wrong?

[Bob Phillips]

Needs tweaking for your workbook

[vba]

Sub Test()
Dim mpCell As Range
Dim mpTemp As String

For Each mpCell In Worksheets("Range").Range("A2:A9")
mpTemp = mpTemp & mpCell.Text & vbLf
Next mpCell
With Worksheets("Expected Results").Range("A2")
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.AddComment mpTemp
End With

End Sub

[/vba]

[rory]

Yup:
[VBA]Sub AddComments2()
'Posted by Dave Ramage, 2001-04-11, misc,
Dim rngCells As Range
Dim varComments
Dim strComment As String

Set rngCells = Sheet1.Range("A3")
varComments = Sheet2.Range("A2:A9").Value
Set rngCells = rngCells.Cells(1)
strComment = Join(Application.Transpose(varComments), vbLf)
'add comments
'does the cell already have a comment? If so, delete it
If Not rngCells.Comment Is Nothing Then rngCellsComment.Delete
' Add new comment
rngCells.AddComment strComment

End Sub
[/VBA]
Regards,
Rory

[Shazam]

Thank you for code it works well but when I tried to name it from a paticular worksheet it debugs these lines. In actuality I have multiple worksheets in my workbook.

[VBA]
Set rngCells = "Expected results".Range("A3")
varComments = "Range".Range("A2:A9").Value
[/VBA]

Is there a way I could name my worksheets in your code?

[Bob Phillips]

Qualify with worksheets

[vba]

Set rngCells = Worksheets("Expected results").Range("A3")
varComments = Worksheets("Range").Range("A2:A9").Value
[/vba]

[Shazam]

Qualify with worksheets

[vba]

Set rngCells = Worksheets("Expected results").Range("A3")
varComments = Worksheets("Range").Range("A2:A9").Value
[/vba]

Thanks xld it's perfect. There is one more thing This code you and rory provided will run in a daily matter so the data flucuates daily in column A. So then I would change the range to A2:A500 to insure to capture the whole data.

My question is instead of having a standard size for the insert comment is there a way to resize the insert comment to the appropriate size depends on the data inside of the insert comment?

[Bob Phillips]

[vba]

Sub Test()
Dim mpCell As Range
Dim mpTemp As String

With Worksheets("Range")
For Each mpCell In .Range(.Range("A2"), .Range("A2").End(xlDown))
mpTemp = mpTemp & mpCell.Text & vbLf
Next mpCell
End With
With Worksheets("Expected Results").Range("A2")
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.AddComment mpTemp
End With

End Sub
[/vba]

[rory]

If you want the comment sized, try adding this line to the end:
[VBA] rngCells.Comment.Shape.TextFrame.AutoSize = True
[/VBA]

Regards,
Rory

[Shazam]

Thank You rory and xld they are perfect!! You guys are great.