Insert multiple cell values to a comment

user_16408209 0
Viewed 74

I have a task to add a comment in a sumtotal by month to show the items involved and the amount. I Want to simplify the works because Ido it by monthly and every job (over 20 jobs)!

I found solutions for a single cell.

I need to add related cell values to one comment for the month.
enter image description here

Option Explicit

Sub CreateComment()

    Dim rng As Range
    Dim cel As Range
    Dim myColumn, myRow As Integer
    
    Set rng = Selection
    myColumn = ActiveCell.Column
    myRow = ActiveCell.Row
    
        
    For Each cel In rng
        If cel.Value <> "" Then
        Range("myColumn" & "1").AddComment [Cell("myRow", "1")).Value & " -$" & Cell("myRow","myColumn")_.value]
        End If
    Next
                
End Sub
comments vba excel
1 Answers

Add Comments to a Range

Option Explicit

Sub AddComments()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim lCol As Long: lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Dim srg As Range: Set srg = ws.Range("A1").Resize(lRow, lCol)
    Dim Data As Variant: Data = srg.Value
    
    Dim r As Long, c As Long, n As Long
    Dim Comm As String
    
    For c = 2 To lCol
        For r = 4 To lRow
            If Len(Data(r, c)) > 0 Then
                n = n + 1
                Comm = Comm & n & ". " & Data(r, 1) & " - " _
                   & Format(Data(r, c), "$#,##0") & vbLf
            End If
        Next r
        If n > 0 Then
            With srg.Cells(1, c)
                .ClearComments
                .AddComment Left(Comm, Len(Comm) - 1)
            End With
            n = 0
            Comm = ""
        End If
    Next c
    
    MsgBox "Comments added.", vbInformation
    
End Sub
user_98140690
Related