How to convert Excel Text to Comments using VBA

Convert Excel Range to Comments using VBA

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