We have seen how to Copy Comments in an Excel Sheet to a Range; now let us see how to do the opposite
Our reference Excel has Text that needs to be converted as Comments on Column E, which needs to be placed as comments
Sub Convert_Text_To_Comments()
Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name
sUser = Application.UserName
For i1 = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
sText = ActiveSheet.Cells(i1, 5).Value
'Deletes Existing Comments
Cells(i1, 3).ClearComments
' Creates Comment
Cells(i1, 3).AddComment
Cells(i1, 3).Comment.Text Text:=sUser & Chr(10) & sText
Next i1
End Sub
If you already have comments and try to AddComment then Runtime Error 1004 will be thrown. That is why it is better to Remove the existing comments (ClearComments) and proceed with Adding new comment
Posts
Thank you. It works very well. But I have in the original document, some blank rows too. And so, I do not want there to create a comment. How could I bypass this? I need, the comment to be created only if is any text in column E. If not, do not make any comment in column C.
ReplyDeleteYou can check for the following
ReplyDeleteIf len(trim(sText)) <> 0 then
... copy comments code
End if
Thanks. I would like to ask how to do something slightly different: from within a cell with a comment attached, I would like to create a macro to add the comment to the existing text within a cell in brackets like this: existing text &
ReplyDeleteWhere exactly do we place this VBA Data to make it work on the spreadsheet? Is it placed within a cell on the same spreadsheet, on another sheet or elsewhere? Thanks in advance for your assistance. I'm using Excel 2010.
ReplyDeleteCool stuff you've got got and you way replace every body. Advanced Excel Training Mumbai
ReplyDelete