Concatenateing and entire row automaticalls

[threearabawys]

Hello everybody


I need to automatically CONCATENATE row 8 of my worksheet into cell A21 of the same worksheet.
The values in row 8 are dynamic so the concatienation should work life a function or a module.

Does anybody have an idea on how this can be achieved ?


Thanks in advance

 

[threearabawys]

I removed the previous code and pasted this one in its place.. but after much fiddling around and a lot of code frankenstien-ing I got this macro:

Public Sub ChChChain()

Dim rSource As Range
Dim rTarget As Range
Dim oCell As Range
Dim sConcat As String

On Error GoTo woops
Set rSource = Sheets("Graph").Range("A8:XFD8")
Set rTarget = Sheets("Graph").Range("A25")

For Each oCell In rSource
sConcat = sConcat & CStr(oCell.Value)
Next oCell

rTarget.Value = sConcat

woops:


End Sub

it gives the intended the result I hope this helps someone.

 

[Rick Rothstein]

I'm really sorry but I pasted in the above code but didn't get a result :S I don't want to be a pain but I really don't know what's going on..

I would still like to try and salvage the event code method for you (as it would be automatic). I want you to try something just to be sure. Open up the VB editor and execute this code in the Immediate Window...

Application.EnableEvents = True

Then put this code back into the Sheet module (which I described how to do in Message #2)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastColumn As Long, JoinedText As String
  LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column
  Application.EnableEvents = False
  If Len(Range("A21")) = 0 Then
    Range("A21").NumberFormat = "General"
  ElseIf LastColumn = 1 Then
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Range("A8").Value
  Else
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Join(Application.Index(Range("A8").Resize(, LastColumn).Value, 1, 0), "")
  End If
  Application.EnableEvents = True
End Sub

and make a change anywhere on the worksheet. Did cell A21 get populated the way you expected now? Just so you know, I had tested, and just now retested, the above code and it works fine here. The important thing is to make sure the code is placed in the sheet module, not a general module (which is where macros, not event code, go).

 

[threearabawys]

I would still like to try and salvage the event code method for you (as it would be automatic). I want you to try something just to be sure. Open up the VB editor and execute this code in the Immediate Window...

Application.EnableEvents = True

Then put this code back into the Sheet module (which I described how to do in Message #2)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastColumn As Long, JoinedText As String
  LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column
  Application.EnableEvents = False
  If Len(Range("A21")) = 0 Then
    Range("A21").NumberFormat = "General"
  ElseIf LastColumn = 1 Then
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Range("A8").Value
  Else
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Join(Application.Index(Range("A8").Resize(, LastColumn).Value, 1, 0), "")
  End If
  Application.EnableEvents = True
End Sub

and make a change anywhere on the worksheet. Did cell A21 get populated the way you expected now? Just so you know, I had tested, and just now retested, the above code and it works fine here. The important thing is to make sure the code is placed in the sheet module, not a general module (which is where macros, not event code, go).

Thank you, now it works great

 

[Rick Rothstein]

Thank you, now it works great

Great! What happened is the previous code must have crashed before it got a chance to execute the Application.EnableEvents=True statement, so they remained turned off (meaning no event code was getting executed). What I had you do is turn them back on manually... after that, the latest code was free to act.

 

[threearabawys]

Thank you so much, you're a genius