Insert Multiple Cell value to a comment-CodePudding

i have a task to add a comment in a sumtotal by month to show the items involved and the amount. i wanna to simplify the works because i need to do it by monthly(normally 12 months) and every job (over 20 jobs)!

i have tried to find solution but seems the solutions are a comment for single cell. But all i need is add related cells value to one comment for the month

tried to use VBA but seems very hard. can anyone give me some advise ? thanks.

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

CodePudding user response：

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