Adding comment to cells not working with merged cells

[mcphc]

I have some code that adds a cells comments to the cell value as below:

Sub CommentsToCells()

Dim cl As Range
Dim rng As Range
Dim cmt As Comment

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

For Each cl In rng
Set cmt = cl.Comment
cl.Value = cl.Value & " : " & cmt.text
Next cl

End Sub

This works well with unmerged cells but when the cells are merged the cell
value is updated but then the code stops with the error:

Run-time error '91':
Object variable or With block variable not set

How can I get this to work for merged cells as well?

 

[Bill Renaud]

All other cells in a merged set of cells should be empty. Only the first
cell actually has data. Skip around setting the new value of the cell,
if it is empty.

If Not IsEmpty(cl) Then cl.Value = cl.Value & " : " & cmt.Text

 

[Bill Renaud]

Correction (I forgot that the cell might be empty, but still have a
comment and not be merged):

Sub CommentsToCells()

Dim cl As Range
Dim rng As Range
Dim cmt As Comment

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

For Each cl In rng
Set cmt = cl.Comment

If IsEmpty(cl) _
Then
cl.Value = " : " & cmt.Text
Else
cl.Value = cl.Value & " : " & cmt.Text
End If
Next cl

End Sub

Your code is crashing in the case where cl.Value is empty (merged or
not). You cannot concantenate an empty variable with a string to get
another string.