variable length concatenation.

E

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
Win XP Professional office 2003 IE 8<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Hello I am looking for some help creating a variable length concatenation.<o:p></o:p>
<o:p> </o:p>
All the data is in column A, the start point will be Row A6 and can extend down an unknown amount of rows, the clue as to were the concatenation ends will be the word date.<o:p></o:p>
<o:p> </o:p>
e.g.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
row 1 some text<o:p></o:p>
row 2 some text<o:p></o:p>
row 3 some text<o:p></o:p>
row 4 some text<o:p></o:p>
row 5 some text<o:p></o:p>
row 6 start of concatination down to row X -1<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
concatination <o:p></o:p>
concatination<o:p></o:p>
row x containing the word “Date”<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
N.B row 6 is where I need the complete string to end up <o:p></o:p>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Give the macro a try...

Code: 
Sub ConCatA7ToDateCell()
  Dim DateCell As Range
  Set DateCell = Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find("Date", , xlValues, xlPart, xlByColumns, xlNext, False)
  If Not DateCell Is Nothing Then
    Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.Row - 1))))
  End If
End Sub
Note: You didn't say, so I assumed you wanted a space between each cell's text.
 
Upvote 0
Rick Rothstein said:
Give the macro a try...

Code: 
Sub ConCatA7ToDateCell()
  Dim DateCell As Range
  Set DateCell = Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find("Date", , xlValues, xlPart, xlByColumns, xlNext, False)
  If Not DateCell Is Nothing Then
    Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.Row - 1))))
  End If
End Sub
Note: You didn't say, so I assumed you wanted a space between each cell's text.
Click to expand...
Hi thanks for the code, I am having some difficulty in get it to work below is the data I tried to no avail, I have walked through your code and it seems reasonable and I have no idea why it has fail to do the task, I was expecting a concatenated string in cell A6 of the two paragraphs but got nothing <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Can you see my error <o:p></o:p>
<o:p></o:p>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; MARGIN: auto auto auto 42pt; WIDTH: 416.5pt; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0 width=555><TBODY><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=555>title: Percy's Park - After the Storm <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>author: Nick Butterworth <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>format: Paperback <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>isbn: 0-00-100448-4 / 978-0-00-100448-1 <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>publisher: HarperCollins <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 9"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 10"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 11"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 46.15pt; mso-yfti-irow: 12"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 46.15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>A storm is raging through the park causing Percy, the park keeper, to have a sleepless night worrying about the damage being done to his beloved park. When he gets up the next morning he finds the old oak tree has been blown down. This is a disaster for all his animal friends who used the tree as their home. Fortunately, Percy is on hand to help find them a new place to live. <o:p></o:p>

</TD></TR><TR style="HEIGHT: 34.8pt; mso-yfti-irow: 13"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 34.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>The simple text and expressive illustrations perfectly combine to create a picture storybook that is ideal for reading aloud to young children. The poster-sized gatefold at the end is an added surprise and is a superb example of Nick Butterworth's skills as an illustrator. (Ages <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:time Minute="58" Hour="5">2 to 6</st1:time>) <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 14"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 15"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>Date<o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 16"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555>Search for Author Book Short story ISBN <o:p></o:p>

</TD></TR><TR style="HEIGHT: 11.35pt; mso-yfti-irow: 17; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 416.5pt; PADDING-RIGHT: 5.4pt; HEIGHT: 11.35pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=555><o:p></o:p>

</TD></TR></TBODY></TABLE>

There error pointed to this line of code

Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.row - 1))))
 
Last edited:
Upvote 0
EDUCATED MONKEY said:
Hi thanks for the code, I am having some difficulty in get it to work below is the data I tried to no avail, I have walked through your code and it seems reasonable and I have no idea why it has fail to do the task, I was expecting a concatenated string in cell A6 of the two paragraphs but got nothing <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Can you see my error
There error pointed to this line of code

Range("A6").Value = Join(WorksheetFunction.Transpose(Range("A7:A" & (DateCell.row - 1))))
Click to expand...
A couple of questions. What did the error message say? Are there any merged cells in Column A?
 
Upvote 0
Run time error -214741848(80010108)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
Method transpose of object worksheet function failed<o:p></o:p>
as far as i know there are no merged celll as the data gets pasted in to column A1
 
Upvote 0
I think we may be running into some kind of text length limit. For your posted example, they are both more than 255 characters in length. If we reduce each of them to a length equal to or less than this number, the code works fine. It appears the Transpose function has a 255 character limit on the text in the cells being transposed. It looks like we will have to use the brute-force method of concatenating your text...

Code: 
Sub ConCatA7ToDateCell()
  Dim X As Long, Text As String
  For X = 7 To Cells(Rows.Count, "A").End(xlUp).Row
    Text = Text & " " & Cells(X, "A").Value
  Next
  Range("A6").Value = WorksheetFunction.Trim(Text)
End Sub
 
Upvote 0
Yes i have been trying to do some thing along those lines, first i used match to find the row the retrieve that value inside the sub to form a loop untill the cell has be check to see if it's blank the concatenating them not had much luck doing yet, its a good job i tried your code otherwise i would not of knowen about the 255 limit and drove my self mad tring to fix it, at least i know the range is now A6 to N, Nbeing the value passed to the sub, if you have any other ideas i would like to try then thanks for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Can't get MailMerge to work through VBA
Replies
0
Views
286
DetroitJimBurke
D
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
507
Peter_SSs
Peter_SSs
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N
C
  • Question Question
VBA Data Munging - deleting unwanted stuff
Replies
2
Views
511
Micron
M
K
  • Solved
Help selecting variable range for cut and paste
2 3
Replies
27
Views
2K
OaklandJim
O

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top