For Each ocell In Range("X1", Range("A1").End(xlDown))

[Andy D 2002]

hi, having a few problems with this one..

cells in column X have an associated value in AB which i want "merged" into one cell in column AJ..is going a bit wrong ..if can check code would be appreciated
thanks
A

Example
Cells X1 and X2 = "Company A"
Cells AB1 and AB2 read "Smith" and "Jones" respectively
I want AJ1 to show "Smith, Jones"

currvalue = ""
concat = ""
flag = 0
For Each ocell In Range("X1", Range("A1").End(xlDown))

If currvalue <> ocell.Value Then
If flagnew = 0 Then
curradd = ocell.Address
concat = ocell.Offset(0, 4).Value
currvalue = ocell.Value
flagnew = 1
Else
Range(curradd).Offset(0, 12).Value = concat
curradd = ocell.Address
currvalue = ocell.Value
concat = ""
concat = ocell.Offset(0, 4).Value
End If
Else
concat = concat & ", " & ocell.Offset(0, 4).Value
End If

Range(curradd).Offset(0, 12).Value = concat
Next ocell