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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top