PDA

View Full Version : Solved: Copy Range and paste it into a insert comment



Shazam
08-01-2007, 07:07 AM
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.


Public Sub test()
Dim x As String
Dim w As Worksheet

w="Range"
x = Range("A2:A9").Value
Range("A2").AddComment x
End Sub




I left a example workbook below.

rory
08-01-2007, 07:23 AM
Try this:
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


Regards,
Rory

Bob Phillips
08-01-2007, 07:26 AM
One simple way



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

Shazam
08-01-2007, 07:28 AM
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
08-01-2007, 07:32 AM
One simple way



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




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
08-01-2007, 07:43 AM
Needs tweaking for your workbook



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

rory
08-01-2007, 07:45 AM
Yup:
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

Regards,
Rory

Shazam
08-01-2007, 07:57 AM
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.


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


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

Bob Phillips
08-01-2007, 08:01 AM
Qualify with worksheets



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

Shazam
08-01-2007, 08:31 AM
Qualify with worksheets



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



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
08-01-2007, 09:02 AM
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

rory
08-01-2007, 09:10 AM
If you want the comment sized, try adding this line to the end:
rngCells.Comment.Shape.TextFrame.AutoSize = True


Regards,
Rory

Shazam
08-01-2007, 09:17 AM
Thank You rory and xld they are perfect!! You guys are great.:friends: