Excel-VBA macro to transform cell content into a comment of another cell

Question

I have a seemingly simple goal to turn the content of column B into comments of column A.

I have tried using the following code from @Dy.Lee mentioned here, but unfortunately it gives me a Run-time error '1004' Application-defined or object-defined error...

Sub Komentari()
Dim rngDB As Range, rngComent As Range
Dim rng As Range
Dim cm As Comment, i As Integer
Set rngComent = Range("A1:A50")
Set rngDB = Range("B1:B50")

For Each rng In rngComent
    i = i + 1
    If Not rng.Comment Is Nothing Then
        rng.Comment.Delete
    End If
    Set cm = rng.AddComment
    With cm
        .Visible = False
        .Text Text:=rngDB(i).value
    End With
Next rng
End Sub

Can somebody, please, spot the mistake or suggest a better solution for this?

Try `.Text Text:=rngDB(i).text` to make sure it's a string? – SJR Aug 16 '18 at 11:25 — SJR, Aug 16 '18 at 11:25
@QHarr - error is on .Text Text:=rngDB(i).value – Arturo Aug 17 '18 at 07:45 — Arturo, Aug 17 '18 at 07:45

score 1 · Accepted Answer · answered Aug 16 '18 at 13:02

I'd go this way (explanations in comments):

Public Sub Komentari()
    Dim rng As Range

    With Range("A1:A50") ' reference comments range
        .ClearComments ' clear its comments
        For Each rng In .Offset(, 1).SpecialCells(xlCellTypeConstants) ' loop through refrenced range adjacent not empty cells
            With rng.Offset(, -1).AddComment ' add comment to current rng corresponding comment range cell
                .Visible = False
                .Text rng.Value2
            End With
        Next
    End With
End Sub

score 0 · Answer 2 · answered Aug 16 '18 at 11:32

Sub Komentari()
Dim rngDB As Range, rngComent As Range
Dim rng As Range
Dim cm As Comment, i As Integer
Set rngComent = Range("A1:A50")

For Each rng In rngComent
    i = i + 1
    If Not rng.Range("B1").Comment Is Nothing Then
        rng.Range("B1").Comment.Delete
    End If
    rng.Range("B1").AddComment (rng.Text)
Next rng
End Sub

score 0 · Answer 3 · answered Aug 16 '18 at 11:32

Something like the following where you can use Offset to get the adjacent range, you drop the = when adding the text value to the comment, test that there is actually a value present first as well, and ensure you state the sheet to avoid implicit Activesheet reference.

Option Explicit
Public Sub Komentari()
    Dim rngComent As Range
    Dim rng As Range, cm As Comment

    With ThisWorkbook.Worksheets("Sheet1")
        Set rngComent = .Range("A1:A50")
        For Each rng In rngComent
            If Not rng.Comment Is Nothing Then
                rng.Comment.Delete
            End If
            Set cm = rng.AddComment
            With cm
                .Visible = False
                If rng.Offset(, 1) <> vbNullString Then .Text rng.Offset(0, 1).Value
            End With
        Next
    End With
End Sub

Rather than add blank comments you could also flip this round to:

Option Explicit
Public Sub Komentari()
    Dim rngComent As Range
    Dim rng As Range, cm As Comment

    With ThisWorkbook.Worksheets("Sheet1")
        Set rngComent = .Range("A1:A50")
        For Each rng In rngComent
            If Not rng.Comment Is Nothing Then
                rng.Comment.Delete
            End If

            If rng.Offset(, 1) <> vbNullString Then
                Set cm = rng.AddComment
                With cm
                    .Visible = False
                    .Text rng.Offset(0, 1).Value
                End With
            End If
        Next
    End With
End Sub

Excel-VBA macro to transform cell content into a comment of another cell

3 Answers3