Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments attached to cells into data
Hi There,
I thought I'd try again to get an answer to this problem. I have a spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Although I received suggestions from Tim and Mike, I could not get either suggestion to work. Sorry guys. Are there any other suggestions I could try? -- Cheers GLR |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments attached to cells into data
Hi,
I missed your response to my post yesterday Right click your sheet tab, view code and paste this in. Currently it uses Column A so change to suit. It will copy comments into the cell to the right of the column you use and then delete the original comment. You can run it from VB editor by taspping F5 or close VB editor File|Close and return to.. Then Tools|Macro|Macros Select 'This WorkBook' Highlight the macro name and click run Sub marine() Application.ScreenUpdating = False Set myrange = Range("A1:a100") For Each c In myrange On Error Resume Next c.Offset(, 1).Value = c.Comment.Text c.ClearComments Next Application.ScreenUpdating = True End Sub Mike "glarosa" wrote: Hi There, I thought I'd try again to get an answer to this problem. I have a spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Although I received suggestions from Tim and Mike, I could not get either suggestion to work. Sorry guys. Are there any other suggestions I could try? -- Cheers GLR |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments attached to cells into data
Hi,
First change your security level-ToolsOptionsSecurityMacro SecurityChoose the €śLow€ť option. Then Copy this code: Sub showcomments() 'posted by Dave Peterson 2003-05-16 Application.ScreenUpdating = False Dim commrange As Range Dim cmt As Comment Dim curwks As Worksheet Dim newwks As Worksheet Dim i As Long Set curwks = ActiveSheet On Error Resume Next Set commrange = curwks.Cells _ .SpecialCells(xlCellTypeComments) On Error GoTo 0 If commrange Is Nothing Then MsgBox "no comments found" Exit Sub End If Set newwks = Worksheets.Add newwks.Range("A1:D1").Value = _ Array("Number", "Name", "Value", "Comment") i = 1 For Each cmt In curwks.Comments With newwks i = i + 1 On Error Resume Next .Cells(i, 1).Value = i - 1 .Cells(i, 2).Value = cmt.Parent.Name.Name .Cells(i, 3).Value = cmt.Parent.Value .Cells(i, 4).Value = cmt.Parent.Address .Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ") End With Next cmt newwks.Cells.WrapText = False newwks.Columns.AutoFit Application.ScreenUpdating = True End Sub Go to your bookAlt+F11InsertModule and Paste the copied Code. Close the VBA EditorSelect your worksheet with the commentsAlt+F8 and Run the macro €śshowcomments€ť. After running the macro it will list all comments from your worksheet regardless in which column are they on a new Worksheet. If you have comments in more the 1 column and you insist to list only the comments from that particular column then use the Mikes suggestion. Hope this helps. Tim "glarosa" wrote: Hi There, I thought I'd try again to get an answer to this problem. I have a spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Although I received suggestions from Tim and Mike, I could not get either suggestion to work. Sorry guys. Are there any other suggestions I could try? -- Cheers GLR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting comments to data | Excel Discussion (Misc queries) | |||
Keeping web query cells attached to other data. | Excel Worksheet Functions | |||
COUNTIF to count cells that have an attached comment | Excel Discussion (Misc queries) | |||
Excel Charts with attached comments option | Charts and Charting in Excel | |||
Converting Comments | Excel Discussion (Misc queries) |