Hi friends,
I want to concatenate the 3 different columns in column 'g, ac and ad' respectively. I'm not getting the correct output in column 'g' and the concatenate output of column 'ac and ad' respectively. The output of concatenate 'ac and ad' displays in a single column 'g' and the concatenate output of column 'g' is not displaying in column 'g'.
Please see the following code and suggest me the correction to get correct output.
Thanking you in anticipation.![]()
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count = 1 Then Select Case .Column Case 28 'Column AB On Error Resume Next Range("b" & .Row + 1).Select On Error GoTo 0 Case 6 'Column F On Error Resume Next Target.Offset(, 2).Select 'This will select the next column to the right from the changed cell On Error GoTo 0 Case 13 'Column M On Error Resume Next Target.Offset(, 2).Select 'This will select the next column to the right from the changed cell On Error GoTo 0 End Select End If End With 'CONCATENATE column g If Target.Row < 7 Then Exit Sub Dim vTemp As Variant Dim var As Variant Dim lngArr As Long var = Array("C", "d", "e", "f") Application.EnableEvents = False For lngArr = LBound(var) To UBound(var) vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " " Next lngArr vTemp = Replace(Trim(vTemp), "-", " ") Cells(Target.Row, 7).Value = vTemp Application.EnableEvents = True 'CONCATENATE column ac If Target.Row < 7 Then Exit Sub var = Array("z", "aa", "ab") Application.EnableEvents = False For lngArr = LBound(var) To UBound(var) vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " " Next lngArr vTemp = Replace(Trim(vTemp), "-", " ") Cells(Target.Row, 7).Value = vTemp Application.EnableEvents = True 'CONCATENATE column ad If Target.Row < 7 Then Exit Sub var = Array("aa", "ab") Application.EnableEvents = False For lngArr = LBound(var) To UBound(var) vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " " Next lngArr vTemp = Replace(Trim(vTemp), "-", " ") Cells(Target.Row, 7).Value = vTemp Application.EnableEvents = True End Sub
Regards,
Mukesh











LinkBack URL
About LinkBacks
Register To Reply
Bookmarks