Remove text from Comment and paste into cell

[BernzG]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[BernzG]

Hi Norman,

Thanks - worked perfectly.

Cheers,
Bernz