how to add text to a comment in VBA
 

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?

how to add text to a comment in VBA
 

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

how to add text to a comment in VBA
 

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

how to add text to a comment in VBA
 

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?