Extract text from comments
 

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

Extract text from comments
 

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


"camlad" wrote in message
...
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

Extract text from comments
 

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
--
Gary''s Student - gsnu2007L


"camlad" wrote:

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

Extract text from comments
 

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" wrote in message
...
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
--
Gary''s Student - gsnu2007L


"camlad" wrote:

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

Extract text from comments
 

Fortunately the OP only has about 500 comments to process.
--
Gary''s Student - gsnu200828


"Bob Alhat" wrote:

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" wrote in message
...
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
--
Gary''s Student - gsnu2007L


"camlad" wrote:

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

Extract text from comments
 

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

Camlad

Extract text from comments
 

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" wrote in message
...
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" wrote in
message ...
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
--
Gary''s Student - gsnu2007L


"camlad" wrote:

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

Extract text from comments
 

Thanks Peter,

My recall was clearly not total, or helpful.

Bob

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" wrote in
message ...
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
--
Gary''s Student - gsnu2007L


"camlad" wrote:

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