VBA Copy comments to a Range in Excel
If a worksheet is dotted with lot of comments and you want to respond to each one of these, it is better to extract the comments and place it in a column. This would help in responding to the comments. The following code will extract all comments and place it in column ‘F’ along with the value of the original cell
Sub Convert_Comment_To_Text()
Dim oCom As Comment
Dim sVal As String
Dim i As Integer
For i = 1 To ActiveSheet.Comments.Count
sVal = ""
Set oCom = ActiveSheet.Comments(i)
sVal = ActiveSheet.Cells(oCom.Parent.Row, oCom.Parent.Column).Value
ActiveSheet.Range("F" & CStr(oCom.Parent.Row)).Value = "'" & sVal & " -- " & oCom.Text
Next
End Sub
Before Running the Macro
Comments placed in Column FSee also:
Delete Comments from Excel Workbook using VBA
Posts
Belive that
ReplyDeleteoCom = ActiveSheet.Comments(i)
SHOULD be
Set oCom = ActiveSheet.Comments(i)
Thanks for your comment. I have corrected it
ReplyDeleteThanks for the tip, will be great help if you can modify to put the comments in the new sheet.
ReplyDeleteThanks for the tips, it will be great to have the comments in the new sheet. Hope you can add this feature.
ReplyDeleteI understand some programming, but I'm not sure what I need to change to make this work. What would the code be if you have comments listed in multiple columns?
ReplyDeleteIt should work for multiple columns as it is extracting from ActiveSheet. Do you mean extracting from Multiple sheets?
ReplyDeleteThanks a lot pal!
ReplyDeleteHi Thanks this is brilliant. I received a spredsheet from a busines partner who had add 20 lines of comments!!!
ReplyDeleteThe app worked perfectly and saved me a lot of time.
Thanks a lot!
How can I convert a column of text to a column of comments? In other words, each entry in the column is converted to a comment..
ReplyDeleteThanks You
Thank you. This will greatly help me. Because I need to translate text in comments to another language. But could you now write this macro vice versa? How can I get the text from column E back to the comments in column C now?
ReplyDeleteHere you have it - http://vbadud.blogspot.in/2012/02/how-to-convert-excel-text-to-comments.html
ReplyDeleteIs there a way to select an individual cell, copy its comment inside brackets at the end of the cell, and delete the comment?
ReplyDeleteAnd is there a way to extract text from within brackets a the end of a cell and rewrite it as a comment/ (reversing the earlier process?)
I am finding comments incredibly hard and time consuming to read in their current format.
This is easier. It sends comments from cells in column 1 to corresponding cells in column 2. Just insert a blank column as column 2, then run macro.
ReplyDeleteSub 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
What if there are comments in multiple columns. For instance in your example... what if you had commented on both Price and Availability for Apples? This macro seems to choose only one of the two comments to extract. Any work around for this?
ReplyDeleteI have the same question as the user who commented at 10:31AM
ReplyDeletedo you know how to parse each line of a comment separately? I tried split(mycomment.text,vbcrlf) and vbcr and vblf without any luck, I still get the whole comment as a multi-line string. Many thanks!
ReplyDeleteMy table range is A:E. In some rows there a more than one comment in a row. Your VBA copies only the last comment in "F".
ReplyDeleteHow could i change it to get all comments in a row in column "F"?
Thanks in advance