Add comment to cell if cell in other column contains value-CodePudding

I have a code that combines the cell contents of all cells in column C:F and puts that into a comment on column B - per row. I now need to apply that only to rows that have content in their respective column A.

Cell A2 has something in it, so put the contents of C2:F2 into the comment of B2. Cell A3 has nothing in it, so don't add a comment to that cell. Cell A4 has something in it again, so put the contents of C4:F4 into the comment of B4.

The table looks something like this: Table

My code so far looks like this:

Sub Test()
    Dim LRow As Integer
    
    With ActiveSheet
        For LRow = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range(Cells(LRow, 3), Cells(LRow, 6)).Select
        
        Dim c As Range, s As String

        With Cells(LRow, 2)
            .ClearComments
            For Each c In Selection
            'If c.Offset(0, -2) <> "" Then
                'On Error Resume Next
                If c <> "" Then s = IIf(s = "", c, s & Chr(10) & c)
                Next c
                .AddCommentThreaded "Test:" & Chr(10) & s
        End With
        s = ""
        Next LRow
    End With
End Sub

Problem now being that I can't get the content check in column A to work. Anyone have any hints on how to get that bit to work?

CodePudding user response：

Try something like below. Also checkout how to avoid select and why use long instead of integer

Sub Test()
    Dim LRow As Long, aCell As Range, ws As Worksheet
    Set ws = ActiveSheet
    
    With ws
    
        For LRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(LRow, 1).Value <> "" Then
                Dim theComment As String
                theComment = ""
                
                For Each aCell In Intersect(Range("C:F"), .Rows(LRow)).Cells
                    theComment = theComment & aCell.Value
                Next aCell
    
            With .Cells(LRow, 2)
                .ClearComments
                    .AddCommentThreaded "Test:" & Chr(10) & theComment
            End With
            End If
            
        Next LRow
    End With
End Sub