Not getting correct output problem

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.

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

Thanking you in anticipation.

Regards,

Mukesh

Hi, Mukesh,

you should take up the tips you get: you only need to check for the row number once.

You could step through the code all by yourself by setting Stop intoc the code near the beginning or add a break by pressing F9 and see what the code is doing - if you want to concatenate the contents of several cells into one cell which holds contents you should not overwrite but add to the existing contents.

Ciao,
Holger

Hi, Mukesh

there is a lot of unnecessary movement in the code, and I pretty much doubt that you know what you are putting down as comment should be put down in the code as well:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim vTemp As Variant
Dim var As Variant
Dim lngArr As Long

If Target.Row < 7 Then Exit Sub

Application.EnableEvents = False

'CONCATENATE column g
var = Array("C", "d", "e", "f")

For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "G").Value = vTemp

'CONCATENATE column ac
var = Array("z", "aa", "ab")

For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "ac").Value = vTemp

'CONCATENATE column ad
var = Array("aa", "ab")

For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "ad").Value = vTemp

Application.EnableEvents = True

End Sub

I didn't understand the second suggestion of break point using f9. Please explain it little more for me for better understanding. On which line the break point should be place?

Put the cursor into the first executable line of code and press F9, from my sample this would be the line

If Target.Row < 7 Then Exit Sub

If the code is fired it will hold on that line and you can step through the rest of the code by pressing F8.

Ciao,
Holger

Hi, Mukesh,

you would neeed to add code lines to empty vTemp as that would hold the value form the array before (sorry I missed that as I didnīt look through the code carefully enough):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim vTemp As Variant
Dim var As Variant
Dim lngArr As Long

If Target.Row < 7 Then Exit Sub

Application.EnableEvents = False

'CONCATENATE column g
var = Array("C", "d", "e", "f")

For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "G").Value = vTemp

'CONCATENATE column ac
var = Array("z", "aa", "ab")
vTemp = ""
For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "ac").Value = vTemp

'CONCATENATE column ad
var = Array("aa", "ab")
vTemp = ""
For lngArr = LBound(var) To UBound(var)
  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, "ad").Value = vTemp

Application.EnableEvents = True

End Sub

Ciao,
Holger

Hi, Mukesh

  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "

adds a blank after each single cellcontent, you might like to check

  vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value

for adding the contents of the cells without a blank.

patience

Who? Me being patient? Not at all ...

Ciao,
Holger