I have a two column table on sheet2. The first column contains cell
addresses and the second are text notes. I want to loop through the cell
address and locate that address on sheet1 and place the text in a comment. I
can do this, but there are cell address that are the same. I want the text
for each to be on a new line in the same comment object.

Can anyone help with this?

Hello Db,

Here is the code. You will need to change the index number to match the
Comment whose text you want to change.

EXAMPLE:
Comments(1).Text "Put this text into the Comment cell."

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=482060

Maybe something like:

Option Explicit
Sub testme()

Dim CmtWks As Worksheet
Dim wks As Worksheet
Dim testRng As Range
Dim myCell As Range
Dim myRng As Range

Set CmtWks = Worksheets("sheet2")
Set wks = Worksheets("sheet1")

With CmtWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set testRng = Nothing
On Error Resume Next
Set testRng = wks.Range(myCell.Value).Cells(1) 'just one cell!
On Error GoTo 0

If testRng Is Nothing Then
myCell.Offset(0, 2).Value = "Invalid Range!"
Else
myCell.Offset(0, 2).ClearContents

If testRng.Comment Is Nothing Then
testRng.AddComment Text:=myCell.Offset(0, 1).Value
Else
testRng.Comment.Text _
Text:=testRng.Comment.Text _
& vbLf & myCell.Offset(0, 1).Value
End If
End If
Next myCell

End Sub

I do use column C for results. You can either delete that portion of the code
or make sure there's nothing in column C.



DB wrote:

I have a two column table on sheet2. The first column contains cell
addresses and the second are text notes. I want to loop through the cell
address and locate that address on sheet1 and place the text in a comment. I
can do this, but there are cell address that are the same. I want the text
for each to be on a new line in the same comment object.

Can anyone help with this?

--

Dave Peterson

You can also use the NoteText object (for a flash from the past)

Sub ABC()
Dim cell As Range
Dim sStr As String
For Each cell In Worksheets("Sheet2") _
.Range("A1:A2")
If Not IsEmpty(cell) Then
On Error Resume Next
Set rng = Worksheets("Sheet1") _
.Range(cell.Value)
On Error GoTo 0
If Not rng Is Nothing Then
If rng.Comment Is Nothing Then
rng.NoteText Text:=cell.Offset(0, 1).Text
Else
sStr = rng.NoteText
rng.NoteText Text:=sStr & Chr(10) & _
cell.Offset(0, 1).Text
End If
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"DB" wrote in message
...
I have a two column table on sheet2. The first column contains cell
addresses and the second are text notes. I want to loop through the cell
address and locate that address on sheet1 and place the text in a comment.
I
can do this, but there are cell address that are the same. I want the
text
for each to be on a new line in the same comment object.

Can anyone help with this?