VBA Converting Cell Contents to Comments - 255 character limit problem

[vba_help_needed]

I need to convert the cell contents in a column into comments. Here is code that nearly accomplished that:

<code>Range.(Select Column Here).Select

Dim c As Range

For Each c In Selection
</code><code> With c
</code><code> .NoteText Text:=.Value
</code><code> End With
</code><code>Next c
</code>
This works for the most part. However, my problem is that cells with > 255 characters don't get converted to comments. Does anyone know a workaround for this? I'm having to manually go and create the comment for the cells with > 255 characters and would really like to find a way to automate this.

Thank you for any attempt to help.

 

[ParamRay]

Cell notes have been replaced by range comments, which have a limit of 32,767 (2^15 - 1) characters.

Try this instead:

Public Sub ValuesToComments()
  Dim rng As Range
  If TypeOf Selection Is Range Then
    For Each rng In Selection.Cells
      On Error Resume Next
      rng.Comment.Delete
      On Error GoTo 0
      rng.AddComment Text:=rng.Value
    Next rng
  End If
End Sub

 

[vba_help_needed]

Cell notes have been replaced by range comments, which have a limit of 32,767 (2^15 - 1) characters.

Try this instead:

Public Sub ValuesToComments()
   Dim rng As Range
   If TypeOf Selection Is Range Then
     For Each rng In Selection.Cells
      On Error Resume Next
      rng.Comment.Delete
       On Error GoTo 0
      rng.AddComment Text:=rng.Value
     Next rng
   End If
End Sub

Thank you so much for your reply. This works for converting cells with > 255 characters, however, it errors out when it gets to a blank cell.

For anyone looking to bum this code I added a simple if statement to combat this.

Public Sub ValuesToComments()
Range(Column whose contents you want to convert to comments).Select
Dim rng As Range
For Each rng In Selection
With rng
If IsEmpty(rng) Then
Else
.AddComment Text:=.Value
End If
End With
Next rng
End Sub