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
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
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
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
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
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
Posted by David Hawley on February 28, 2001 5:07 AM
Here it is!!
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
Posted by Chas on February 28, 2001 10:43 AM
Re: Here it is!!
, 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
