try this
Sub Comment_To_Content() 'pastes the comments in a range as cell
'contents in another range
Dim C As Range 'the selected range
Dim UserRng As Range ' the receiving range
Dim dest As Long, RwNum As Long
Dim content As Variant, response As Variant
'PRELIMINARIES
Dim A_WSF As Object
Set A_WSF = Application.WorksheetFunction
'STEP 1 ENSURE USER HAS A RANGE SELECTED
If Selection.Cells.Count < 1 Then
Exit Sub
End If
'STEP 2 ASK USER TO SPECIFY THE OUTPUT LOCATION
content = "Choose the column which is to receive the comments."
content = content & Chr(10) & Chr(10) & "They will overwrite any
current entries"
content = content & Chr(10) & Chr(10) & "They will appear on the
SAME ROW as the comment."
Title = "OUTPUT LOCATION"
On Error Resume Next ' in case a range does not get selected
'The use of the "Set" statement assigns the output to the selected
ActiveCell
Set UserRng = Application.InputBox(prompt:=content, Title:=Title,
_
Default:=ActiveCell.Address, Type:=8) '"Type 8" means a Range
result.
If UserRng Is Nothing Then 'input was box cancelled
MsgBox "Operation cancelled by user"
Exit Sub ' Rev A
End If
On Error GoTo 0
dest = UserRng.Column 'this is the column that gets the comments
'STEP 3 CHECK USER HAS SELECTED A COLUMN AWAY FROM THE DATA
If Selection.Column = dest Then ' same column was chosen
content = "You have chosen the same column that your data is
in !"
content = content & Chr(10) & "Your comments will overwrite
your data !"
content = content & Chr(10) & "Are you SURE you want to do
this?"
response = MsgBox(content, 20, " PLEASE EXPLAIN !")
If response <> 6 Then 'user chose something other than YES
Exit Sub
End If
End If
'STEP 4 OBTAIN THE COMMENT AND INSERT IN THE CORRESPONDING ROW OF THE
SELECTED COLUMN.
On Error Resume Next 'There may be no comments to paste
For Each C In Selection
RwNum = C.Row
content = "" 'clear down the variable (just in case)
content = C.Comment.Text
If Len(content) > 0 Then 'don't insert a blank if there
was no comment
Cells(RwNum, dest).Formula = A_WSF.Clean(content)
End If
Next C
On Error GoTo 0
'CONCLUSION
response = MsgBox("FINISHED", vbOKOnly, "COPY COMMENTS")
End Sub
On Jun 29, 2:01 pm, Heather <[email protected]> wrote:
> I would like to extract comments and paste it next to the cell.
>
> For example:
> The comment in cell A1 would be pasted in B1
> The comment in cell A2 would be pasted in B2
> until the bottom row of the data.
>
> If this possible or does the fact that comments are objects prohibit
> this?
>
> Any help is appreciated - I am trying to make sense of data where
> comments were overused!
>
> Thanks!
>
> Heather
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
