Concatenate with style

[DocAElstein]

I think, for now, I will leave this thread with the temporary conclusion that there is probably a "bug" in the (write) property of VBA Character.Text
The "bug" prevents you from using VBA Character.Text on cells with more than 255 characters.

Possibly later I will look or ask around further.

Limitation / problem with Rick's macro
The main problem in Rick's macro resulting from this "bug" is expolained as follows:
_ The macro uses , for convenience, the Character.Text and Space( ) function to put in a number of characters , ( spaces ) , the number of them being equal to the number of characters finally needed in the final text. This is not a problem.
_ But then in the loop for all characters in the final wanted text, ( With Range("A3") ____1 For X = 1 To Len(Cell.Value) ) , the first thing that is done is that the actual text characters from a cell is put in. This will fail if we have put previously more than 255 spaces in.

One way to overcome the problem this is to paste in the entire concatenated text ( including the in between spaces ) in one go, as an alternative way to get the text in. I include this idea and a fe other things in a new macro…

New macro
I the meantime I have picked up enough info of what is going on to be able to write a complete new macro.
Here it is: https://excelfox.com/forum/showthrea...ll=1#post15180
It is written to work on any worksheet selection, but to hard code it simply set RngSel to the range required
It is written ( hard coded ) to give the output in cell A3

Example, select E1:F1
SelectA1E1.jpg : http://i.imgur.com/FS3Mnlz.jpg


_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	=A4 & " " & C7	The	thinking	man's	MrEXCEL	Tips & Solutions

Worksheet: Sheet1

Run macro, Sub ConcatWithStyles3c() ,
Results:
ResultsSelectA1F1.JPG http://i.imgur.com/ju1NmEj.jpg





Example: Select D7:D8
SelectD7D8.JPG http://i.imgur.com/hlxGff2.jpg


_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	D
7	=A4
8	FoX

Worksheet: Sheet1

Run macro, Sub ConcatWithStyles3c() ,
Results:
ResultsSelectD7D8.JPG http://i.imgur.com/vjOy9ck.jpg

---

RicksConcatWithStyles.xls : https://app.box.com/s/n3rfg981htv37p5xjfz9y4v17ycoybqe

---

Misc refs for later use
https://www.myonlinetraininghub.com/...-words-in-text

[Abdul]

your VB code Sub ConcatWithStylesTemporarySolution()
solves all my problems. I appreciate your patience and work for finding what is the problem in Ricks VB. You are great, once again lot of thanks for giving your valuable time.