Remove text from Comment and paste into cell
 

Hi,

I have a file where there are many cells with comment boxes attached t
them. What I am wanting to do is to have a macro that will :-

1. find each comment box
2. cut or copy the contents of the comment box
3. and place the text into a cell in the next column

Is there a way to do this with a macro or do I have to do it manually?

Cheers,
Bern

--
Bernz
-----------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...fo&userid=2294
View this thread: http://www.excelforum.com/showthread.php?threadid=39283

Remove text from Comment and paste into cell
 

Hi Bercz,

Try:

'==========================
Public Sub Tester03()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet '<<======= CHANGE
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng
rCell(1, 2).Value = rCell.Comment.Text
Next rCell
End If

End Sub
'<<==========================


---
Regards,
Norman



"BernzG" wrote in
message ...

Hi,

I have a file where there are many cells with comment boxes attached to
them. What I am wanting to do is to have a macro that will :-

1. find each comment box
2. cut or copy the contents of the comment box
3. and place the text into a cell in the next column

Is there a way to do this with a macro or do I have to do it manually?

Cheers,
Bernz


--
BernzG
------------------------------------------------------------------------
BernzG's Profile:
http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=392832

Remove text from Comment and paste into cell
 

Hi Bercz,

To additionally delete the comment text or, alternatively, to delete the
comments, try:

'==========================
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng
With rCell
.Item(1).Value = rCell.Comment.Text
.Comment.Delete

'To create a blank comment, un-comment the next line
' .AddComment Text:=""

End With
Next rCell
End If

End Sub
'<<==========================

To delete all comments, use as above.

To replace the comments with blank comments, un-comment (remove the initial
apostrophe) in the line:

' .AddComment Text:=""


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Bercz,

Try:

'==========================
Public Sub Tester03()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet '<<======= CHANGE
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng
rCell(1, 2).Value = rCell.Comment.Text
Next rCell
End If

End Sub
'<<==========================


---
Regards,
Norman



"BernzG" wrote in
message ...

Hi,

I have a file where there are many cells with comment boxes attached to
them. What I am wanting to do is to have a macro that will :-

1. find each comment box
2. cut or copy the contents of the comment box
3. and place the text into a cell in the next column

Is there a way to do this with a macro or do I have to do it manually?

Cheers,
Bernz


--
BernzG
------------------------------------------------------------------------
BernzG's Profile:
http://www.excelforum.com/member.php...o&userid=22949
View this thread:
http://www.excelforum.com/showthread...hreadid=392832

Remove text from Comment and paste into cell
 

Hi Norman,

Thanks - worked perfectly.

Cheers,
Bernz


--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=392832