CONCATENATE

K

kevin carter

Hi
We have this section of code that works it joins the text in the
following format.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-25]C[12],"" "",R[-25]C[13],R[-24]C[12],""
"",R[-24]C[13])"

The result is "four five six seven"

What we are trying to do is split the result using the code below to
display
the following result
"four five
six seven"

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-25]C[12],"" "",R[-25]C[13], & Chr(10) &
,R[-24]C[12],"" "",R[-24]C[13])"

is it posible?

thanks in advance

kevin
 
F

Frank Stone

you might have better luck with something like this.
Selection.TextToColumns Destination:=Range("yourcell"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True,
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1))
 
D

Dave Peterson

Since you're plopping this into a cell as a formula, you'll want to use the
=Char() version.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-25]C[12],"" "",R[-25]C[13], Char(10)," & _
"R[-24]C[12],"" "",R[-24]C[13])"

And it looked like you wanted to drop the =concatenate() function and use & to
join your strings:

ActiveCell.FormulaR1C1 = _
"=R[-25]C[12]&"" ""&R[-25]C[13] & Char(10) & " & _
"R[-24]C[12]&"" ""&R[-24]C[13]"

Seemed to give the same results for me.



kevin said:
Hi
We have this section of code that works it joins the text in the
following format.

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-25]C[12],"" "",R[-25]C[13],R[-24]C[12],""
"",R[-24]C[13])"

The result is "four five six seven"

What we are trying to do is split the result using the code below to
display
the following result
"four five
six seven"

ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-25]C[12],"" "",R[-25]C[13], & Chr(10) &
,R[-24]C[12],"" "",R[-24]C[13])"

is it posible?

thanks in advance

kevin
Click to expand...
 
Top