VB Code to Add Comments to Cells from Several Other Cells

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Need to add comments with a macro or VB code. Want to collect the
contents of several other cells, add them together as a text string
and then put them into a particular cell as a comment.

For example, I want to add comments to cells a1:a5 in Sheet1. Sheet2
contains the data I want to include in the comments in cells a1:c5.
The comment for cell a1 in Sheet1 needs to include the contents of
cells a1:c1 from Sheet2. Comment for a2 in Sheet1 comes from cells
a2:c2.

Thanks for any suggestions or sample code.

Mark
 
Sub Addcomments()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("sheet1")
Set sh2 = Worksheets("sheet2")
Dim cell As Range
For Each cell In sh1.Range("A1:A5")
On Error Resume Next
cell.Comment.Delete
On Error GoTo 0
cell.AddComment Text:=sh2.Cells(cell.Row, 1) & _
" " & sh2.Cells(cell.Row, 2) & " " & _
sh2.Cells(cell.Row, 3)
Next
End Sub
 
Back
Top