Code to add a comment and the text from a sheet range
 

I can easily write code to add a comment to cell C1, with the comment text hard coded.

I am hung up on how to add text to the comment where that text is a variable list of names in cells Range("P2", Range("P2").End(xlDown)).

Also, the text in the comment to be a single name per line.

Thanks,
Howard

Code to add a comment and the text from a sheet range
 

Hi Howard,

Am Fri, 14 Aug 2015 18:19:19 -0700 (PDT) schrieb L. Howard:

I can easily write code to add a comment to cell C1, with the comment text hard coded.

try:

Sub CommentMulti()
Dim varData() As Variant
Dim Lrow As Long, i As Long
Dim strComment As String
Dim rngC As Range

Lrow = Cells(Rows.Count, "P").End(xlUp).Row
ReDim Preserve varData(Lrow - 2)
For Each rngC In Range("P2:P" & Lrow)
varData(i) = rngC
i = i + 1
Next
strComment = Join(varData, Chr(10))

With Range("C1")
.AddComment strComment
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

Sub CommentSingle()
Dim varData As Variant
Dim i As Long, Lrow As Long

Lrow = Cells(Rows.Count, "P").End(xlUp).Row
varData = Range("P2:P" & Lrow)

For i = LBound(varData) To UBound(varData)
Cells(i, "D").AddComment varData(i, 1)
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to add a comment and the text from a sheet range
 

Hi Claus,

Wow, much more code than I thought ever would be needed.

Both work very nice with the Sub CommentMulti() blowing it out of the water!

I'm surprised how little I was able to find searching for examples like these you posted.

Many thanks, Claus.

Howard