• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dear Narayank 1026

@NARAYANK991


Hi!

Ok, but no more than that... we haven't got all the time in the world, have we?... so hurry up... :)

Regards!
 
Dear Sir,


Please find the link


https://rapidshare.com/files/1074754786/Attendance__January_-__2012_.xlsx


The attendance sheet contains a specific comment in each cell mentioning the status of particular person on particular date.


Now my question is, whether we can get all comments together in separate sheet or Page in such way that all comment can be directly seen against cell of same person & given date ?


Draging of all cell & show comment option is not satisfactory as multi comments cell seems to be overlapping to each other . so wanted any other way out.
 
Hi Suthar ,


I have emailed you your workbook. For the benefit of others who may come across this post , the VBA procedure is as follows :

[pre]
Code: 
Public Sub Copy_Comments()
Set Comments_collection = ThisWorkbook.Worksheets("Sheet1").Comments
Set Copy_Sheet = ThisWorkbook.Worksheets("Comments")
For Each cell In Comments_collection
auth = cell.Author
comment_in_full = cell.Text
comment_text = Right(comment_in_full, Len(comment_in_full) - InStr(auth, comment_in_full) - Len(auth) - 2)
Copy_Sheet.Range(cell.Parent.Offset(-1, 0).Address).Value = comment_text
Next
End Sub
[/pre]

Narayan
 
Hi Suthar ,


The workbook I emailed you had the worksheet labelled "Comments" already added to it. If this is not present in your workbook , you will get this error.


Make a copy of your "Sheet1" tab and rename this new tab "Comments" ; delete all the data and comments in the range D6:AH41 ; now run the procedure.


Narayan
 
You must log in or register to reply here.
Back
Top