Search code examples
excelconcatenation

In MS-Excel how to append on existing cell value?

excel image 1

excel image 2

I want to create cell wise log. The first image shows concat value in the third column. Now if I change the value in the first and second columns, I want the new concat value to be added [append] in the third column, not removing old data.

How to do that?

enter image description here

Solution

  • Solved the issue with VBA in Excel. added comment in this below code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo myerror    
    Application.EnableEvents = False
    Dim c As Range

    'K1:AC1 is the column range where the date field exits
    For Each c In Range("K1:AC1")
        'if the cell value has the current date
        If c.Value = Date Then
            'if column E is blank
            If Cells(Target.Row, "E").Value = "" Then
                Cells(Target.Row, c.Column).Value = "created today"
            'if column E is not blank
            Else
                'Cells(Target.Row, c.Column).Value = 'created today'
                
                'Cells(Target.Row, "H").Value = 'Gobinda Nandi (Dev)'
                'Cells(Target.Row, "E").Value = '(Status: In Progress)'
                'Cells(Target.Row, "J").Value = 'WIP'

                Cells(Target.Row, c.Column).Value = Cells(Target.Row, c.Column).Value & vbNewLine & "[" & Format(Now, "HH:MM Am/Pm") & "] " & Cells(Target.Row, "H").Value & " (Status: " & Cells(Target.Row, "E").Value & "), Comment: " & Cells(Target.Row, "J").Value
                'OUTPUT:
                'created today
                '[11:37 AM] Gobinda Nandi (Dev) (Status: In Progress), Comment: WIP
            End If
        End If
    Next c
    'making empty cell
    Cells(Target.Row, "J").Value = ""

    myerror:
        Application.EnableEvents = True
        'error handling code
End Sub

    OUTPUT:

    Output