Extract text from comments

[camlad]

First

I need to extract the text from comments in approximately 500 cells in
column B and place the text into the cells in column C. I am not talking
about simply moving the comment - he text needs to go from comment to
adjoining cell.



A bonus would be to delete the comment.



Please can you help me with a macro to do this automatically?



Second

Conversely I need a macro to do the reverse - that is to take the text from
a cell and put it into a new comment in the adjoining cell - let us say from
column B to column C



Thank you



Camlad

 

[Bob Alhat]

Sub ExtractComment()
Dim cel As Range
For Each cel In Selection
cel.Offset(0, 1).Value = cel.Comment.Text
cel.Comment.Delete
Next cel
End Sub

Sub CreateComment()
Dim cel As Range
For Each cel In Selection
cel.AddComment cel.Offset(0, -1).Value
Next cel
End Sub


HTH
Bob

 

[Gary''s Student]

Sub movem()
Set r = Cells.SpecialCells(xlCellTypeComments)
Set r = Intersect(r, Range("B:B"))
For Each rr In r
rr.Offset(0, 1).Value = rr.Comment.Text
Next
End Sub

 

[Bob Alhat]

Good point Gary's Student:
My code needs the insertion of 'On Error Resume Next' just inside the
For Loop
Your code will be quicker, but is limited by the SpecialCells limit
(about 8900 cells, as I recall)

(My compliments to Gary)

Regards
Bob

 

[Gary''s Student]

Fortunately the OP only has about 500 comments to process.

 

[camlad]

Wow! What a prompt response.
Many thanks Bob and Gary"y Student.

Camlad

 

[Peter T]

but is limited by the SpecialCells limit (about 8900 cells, as I recall)

There's not directly a cell qty limit but there's 8192 non-contiguous areas
limit when SpecialCells is used in VBA.

Regards,
Peter T

 

[Bob Alhat]

Thanks Peter,

My recall was clearly not total, or helpful.

Bob