Archive of Mr Excel Message Board


Back to Edit in Excel archive index
Back to archive home

Comment Copy

Posted by Chas on February 27, 2001 7:45 PM
I hope this is not a duplicate request as I could not find one similar. I have a sheet with several thousand rows. In one column, about half of the cells have comments attached. We are in need of transfering the data to a Access DB and at this point we will lose all data that has been entered into a comment. I am thinking there must be a way of copying the data in a comment to an adjacent cell so that we may save that data for the DB.
Any help is greatly appreciated.
Chas

Re: Comment Copy

Posted by David Hawley on February 27, 2001 8:05 PM


Hi Chas

Select your entire column and copy it. Select the cell in the first row of any other column and go to Edit>PasteSpecial-Comments.

OzGrid Business Applications


Re: Comment Copy

Posted by Chas on February 27, 2001 8:20 PM


Hi Dave,
Thanks but that just pastes the same comment in that cell, not the text from the comment. It still will not be visible in Access.
Chas


Re: Comment Copy

Posted by David Hawley on February 27, 2001 9:03 PM

Sorry Chas, I misunderstood you. Run this macro it will do it for you. It will put the text fom all comments into Column B


Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
Cells(i, 2) = ActiveSheet.Comments(i).Text
Next i


End Sub


OzGrid Business Applications


Re: Comment Copy

Posted by Dave Hawley on February 27, 2001 9:38 PM


OK, I just picked up on the "ajacent" cell bit, I really should see about getting glasses :)

Use this one instead.

Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i


End Sub

It's what you want....Honest!


Dave


OzGrid Business Applications


Re: Comment Copy

Posted by Chas on February 28, 2001 3:47 AM

Hi Dave,
That did work as far as copying the text. I was hoping it could copy to an adjacent cell from each commented cell. It put all comments in column B from 1 thru 825 but they are not in line or connected with the reference cell. I would insert a column next to the column with commented cells and the macro would copy comment text from cell C3 and paste it in cell D3. That way I know which comment goes with which row of information. As it tuns out there are at least 2 columns that have the comments in them as well. I will play with your macro to see what I can do however I am no expert. :) Thanks for the help as it gets me going in the right direction.
Chas


Here it is!!

Posted by David Hawley on February 28, 2001 5:07 AM

Chas, you must have overlooked my last post, so here it is again.


Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i

Dave


OzGrid Business Applications


Re: Here it is!!

Posted by Chas on February 28, 2001 10:43 AM
, you must have overlooked my last post, so here it is again.

Hi Dave,
Thank you so much. That one works perfectly as envisioned. I don't know how I missed the previous post other than maybe my need for glasses as well.
Thankx again,
Chas


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.