MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by terryl on August 17, 2001 10:29 AM

may i take a list (400 cells) of words and place them on cell as comments automatically or must i have someone type them in manually - the cells will have other data in them - thanks

Posted by Damon Ostrander on August 17, 2001 10:23 PM

Hi Terryl,

This is quite easy. Here is an example. Suppose the list of cells containing the words you want to add is G1:G400, and the cell you want to place the comment in is C5. In addition, you probably want a space put between each word. Here's the code:

Dim C As Comment
Set C = [C5].Comment

For iRow = 1 to 400
' Column G is column 7
' First C.Text is Text method, second is
' text property
C.Text C.Text & Cells(iRow,7) & " "
Next iRow

Of course, this is minimal formatting. If you want a carriage return after each word so they appear as a columnar list in the comment, just replace the " " with vbLF.

Happy computing.