Discover the cell address of an attached comment?

M

MiD-AwE

I am trying to read information from a comment and then get the address of
the cell the comment is attached to before writing the information into the
same cell in an other workbook. So for instance, If comment(1) in
workbook(a).worksheet(a) is @ range("I92") then I want to write comment(1)'s
string to workbook(b).worksheet(?).range("I92")

I've been attempting to use:

Workbooks("b").Worksheets("b").Activate
ActiveSheet.Range(CellComnt.Parent.Column & CellComnt.Parent.row).text =
Workbooks("a").Worksheets("a").Comments(1).Text

with no success. Please, I'm exasperated, can someone help? (I'm using Excel
2003)
 
B

Barb Reinhardt

I got this to write the comments on worksheet 1 (the activesheet) to
worksheet 2.

Sub FindComments()
Dim CellCmnt As Comment

Dim aWS As Worksheet
Set aWS = ActiveSheet 'Which happens to be Worksheets(1)
Set oWS = Worksheets(2)

For Each CellCmnt In aWS.Comments
oWS.Range(CellCmnt.Parent.Address).Value = CellCmnt.text
Next CellCmnt

End Sub
 
Top