macro code for grouping adjacent cells into one multi-line cell

[M John]

I am trying to come up with a way to combine cells into one multi-line cell
via a macro or a combination of worksheet functions. I know about
"alt-enter" and vaguely understand "offset". Is there a way to do this?

example:
cell1 cell2 cell3 cell4
1
1 2 3 2
3
Many thanks in advance.

 

[sebastienm]

Hi,
Go throught the following steps:
-Assuming the data is in cell A1, A2, A3
-using CHAR(10) to get a newline in the cell
- make sure the cell is formatted for 'wrap text':
menu Format>Cell, tab Alignment, checkbox 'Wrap Text'
- formula
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
or
= A1 & CHAR(10) & B1 & CHAR(10) & C1

 

[M John]

You are a hero. Solution works perfectly. Many thanks.

M John

 

[M John]

Is there a way to automate the coloring of the text? For instance, in the
resulting cell with the formula:
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
Could the a1 value be red, the b1 value blue, and the c1 value green?
I've tried a macro that has:
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"
but subsequent lines always end up with the values from the cells I'm using
to create the macro:
ActiveCell.FormulaR1C1 = "400" & Chr(10) & "5" & Chr(10) & "8484"
Any help you can provide will be most appreciated.
Thanks,
M John

 

[sebastienm]

- On a cell containing a formula, i don't think you can do it.
- If you copy/paste as value only, the formula is removed from the cell and
the value is kept, then you can do multiple coloring.
Try on the active cell (non-formula cell)
activecell.Characters(1,3).Font.ColorIndex=3

That is, as a summary of the whole process, you would have to do:
- Apply the concatenate formula to the range
- copy/paste special as value (to keep values only)
- loop through the whole range , cell by cell and color, something like:
activecell.Characters(1,3).Font.ColorIndex=3

Dim rg as Range, cell as range
Dim txtpos as long, txrlen as long

set rg= range("D350")
for each cell in rg.cells
txtpos=1
txtlen=len(cell.offset(0,-3).text) 'length of text in A
cell.Characters(1,txtlen).Font.ColorIndex=3 'color in red
txtpos=txtpos + txtlen + 1 ' +1 for the newline character
txtlen=len(cell.offset(0,-2).text) 'length of text in B
cell.Characters(txtpos,txtlen).Font.ColorIndex=20 'search for blue, i
don't know
txtpos=txtpos + txtlen + 1 ' +1 for the newline character
txtlen=len(cell.offset(0,-1).text) 'length of text in C
cell.Characters(txtpos,txtlen).Font.ColorIndex=30 'search for green,
i don't know
next

Note: search for the color index. It correspond to your palette i believe
(menu Tools>Options, tab COlor)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>

Is there a way to automate the coloring of the text? For instance, in the
resulting cell with the formula:
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
Could the a1 value be red, the b1 value blue, and the c1 value green?
I've tried a macro that has:
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"
but subsequent lines always end up with the values from the cells I'm using
to create the macro:
ActiveCell.FormulaR1C1 = "400" & Chr(10) & "5" & Chr(10) & "8484"
Any help you can provide will be most appreciated.
Thanks,
M John

Hi,
Go throught the following steps:
-Assuming the data is in cell A1, A2, A3
-using CHAR(10) to get a newline in the cell
- make sure the cell is formatted for 'wrap text':
menu Format>Cell, tab Alignment, checkbox 'Wrap Text'
- formula
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
or
= A1 & CHAR(10) & B1 & CHAR(10) & C1

 

[M John]

Works beautifully. The first time through, the result is: (for example)
2.167âª1.033âª9.604
but it's correctly colored. This is corrected by clicking on the cell as if
to edit it and then hit return, and it snaps to the:
2.167
1.033
9.604
which I can't explain, but I'm working to figure out.
Thank you so much for your help. It's most appreciated.

- On a cell containing a formula, i don't think you can do it.
- If you copy/paste as value only, the formula is removed from the cell and
the value is kept, then you can do multiple coloring.
Try on the active cell (non-formula cell)
activecell.Characters(1,3).Font.ColorIndex=3

That is, as a summary of the whole process, you would have to do:
- Apply the concatenate formula to the range
- copy/paste special as value (to keep values only)
- loop through the whole range , cell by cell and color, something like:
activecell.Characters(1,3).Font.ColorIndex=3

Dim rg as Range, cell as range
Dim txtpos as long, txrlen as long

set rg= range("D350")
for each cell in rg.cells
txtpos=1
txtlen=len(cell.offset(0,-3).text) 'length of text in A
cell.Characters(1,txtlen).Font.ColorIndex=3 'color in red
txtpos=txtpos + txtlen + 1 ' +1 for the newline character
txtlen=len(cell.offset(0,-2).text) 'length of text in B
cell.Characters(txtpos,txtlen).Font.ColorIndex=20 'search for blue, i
don't know
txtpos=txtpos + txtlen + 1 ' +1 for the newline character
txtlen=len(cell.offset(0,-1).text) 'length of text in C
cell.Characters(txtpos,txtlen).Font.ColorIndex=30 'search for green,
i don't know
next

Note: search for the color index. It correspond to your palette i believe
(menu Tools>Options, tab COlor)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>

Is there a way to automate the coloring of the text? For instance, in the
resulting cell with the formula:
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
Could the a1 value be red, the b1 value blue, and the c1 value green?
I've tried a macro that has:
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"
but subsequent lines always end up with the values from the cells I'm using
to create the macro:
ActiveCell.FormulaR1C1 = "400" & Chr(10) & "5" & Chr(10) & "8484"
Any help you can provide will be most appreciated.
Thanks,
M John

Hi,
Go throught the following steps:
-Assuming the data is in cell A1, A2, A3
-using CHAR(10) to get a newline in the cell
- make sure the cell is formatted for 'wrap text':
menu Format>Cell, tab Alignment, checkbox 'Wrap Text'
- formula
=CONCATENATE( A1 , CHAR(10) , B1 , CHAR(10) , C1 )
or
= A1 & CHAR(10) & B1 & CHAR(10) & C1

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


:

I am trying to come up with a way to combine cells into one multi-line cell
via a macro or a combination of worksheet functions. I know about
"alt-enter" and vaguely understand "offset". Is there a way to do this?

example:
cell1 cell2 cell3 cell4
1
1 2 3 2
3
Many thanks in advance.