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