Concatenate on a dynamic column

  • I am trying to concatenate two columns but only when both have text in them as well as a ", " in between. I get a "Application defined or object defined error" , when I hit debug it highlights the line that has the concatenate formula. Any help is appreciated.


    'poor attempt at VBA programing

    Dim i As Long, a As Integer

    a = 1

    i = 2

    Do While i <= LastRow




    If a = 0 Then


    ElseIf Cells(i, 111) = "" Then Cells(i, 113) = Cells(i, 112)


    ElseIf Cells(i, 112) = "" Then Cells(i, 113) = Cells(i, 111)


    ElseIf Cells(i, 113) = ", " Then Cells(i, 113).Value = ""


    'this should work, why doesn't it work?

    Else:

    ActiveCell = Cells(i, 113)

    Cells(i, 113) = "=concatenate(RC[-2], $DJ$1 , RC[-1])" 'the one with the problem


    End If


    i = i + 1

    Loop

  • try this on a copy of your workbook just in case!


    Code
    1. Sub concat()
    2. Dim c As Range
    3. For Each c In Range(Cells(1, 111), Cells(Rows.Count, 111).End(xlUp))
    4. If c.Value <> "" And c.Offset(, 1).Value = "," And c.Offset(, 2).Value <> "" Then
    5. c.Offset(, 3).Value = c.Value & Range("DJ1").Value & c.Offset(, 2).Value
    6. End If
    7. Next c
    8. End Sub