I have a problem for the below code - it cannot generate the result to "=XXX XXX XXX XXX" (XXX = number) is it anything wrong ?

There are 3 parts Part A : Add comment <---run properly Part B : Add username/editor's name into comment <---run properly Part C : show the sum total but in form of "=xxx xxx xxx xxx" not "=Sum(yyy:zzz)" <---no error but no result

Column F is Sum total and Row 9 is items name, Range I10 to last row and last column is a table, but last row and column is variable

Option Explicit

Public Sub AddComments()

    Dim ws As Worksheet: Set ws = ActiveSheet 'sheet select
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    Dim lCol As Long: lCol = ws.Cells(9, ws.Columns.Count).End(xlToLeft).Column
    Dim srg As Range: Set srg = ws.Range("A1").Resize(lRow, lCol) 'Used area
    Dim Data As Variant: Data = srg.Value 'value in used area
    Dim r As Long, c As Long, n As Long
    Dim Comm As String
    Dim fitcomment As Comment
    
    For r = 22 To lRow
        For c = 9 To lCol
            If Len(Data(r, c)) > 0 Then
                n = n   1
                Comm = Comm & n & ". " & Data(9, c) & " -" _
                   & Format(Data(r, c), "$#,##0") & vbLf
            End If
        Next c
        If n > 0 Then
            With srg.Cells(r, 6)
                .ClearComments
                .AddComment Left(Comm, Len(Comm) - 1)
            End With
            n = 0
            Comm = ""
        End If
    Next r

    For Each fitcomment In Application.ActiveSheet.Comments 'Add User Name
        fitcomment.Text Text:=Environ$("Username") & vbLf, Start:=1, Overwrite:=False
        fitcomment.Shape.TextFrame.Characters(1, Len(Environ$("UserName"))).Font.Bold = True
        fitcomment.Shape.TextFrame.AutoSize = True
    Next


    For r = 22 To lRow
        c = 9
        Comm = "="
        Do
            If ws.Cells(r, c).Value <> "" And IsNumeric(ws.Cells(r, c)) And ws.Cells(r, c).Value <> 0 Then
                Comm = Comm & " " & ws.Cells(r, c).Value
            End If
            c = c   1
        Loop While Not (c > ws.UsedRange.Columns.Count)
        If Comm <> "=" Then
            ws.Cells(6, r).Value = Comm
        Else
            ws.Cells(6, r).Value = ""
        End If
    Next r
    
    MsgBox "Comments added.", vbInformation
    
End Sub

CodePudding user response：

It looks like you've transposed the row/column arguments for ws.Cells when writing the Comm result.

The code is generating a formula string, but it is being written to row 6 starting in column 22('V'). Cell V6 is probably beyond the columns displayed on you monitor - which explains why it looks like there is no output.

   If Comm <> "=" Then
        ws.Cells(6, r).Value = Comm
    Else
        ws.Cells(6, r).Value = ""
    End If

Should be:

   If Comm <> "=" Then
        ws.Cells(r, 6).Value = Comm
    Else
        ws.Cells(r, 6).Value = ""
    End If

BTW, the summation String that you are generating is of the form "= XXX XXX XXX". Excel is fixing this up for you to be "=XXX XXX XXX".