Mix of relative and absolute?
 

I'm working on a macro that has the following line:

ActiveCell.FormulaR1C1 =
"=CONCATENATE(CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

And I want to insert text before the first character return...as in:

ActiveCell.FormulaR1C1 =
"=CONCATENATE("text",CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

Is this possible?
Thanks,
M John

Mix of relative and absolute?
 

Yes, or at least it should be - the formula is simply a string - for
example recording a simple macro that concatenates two cells with a
space between them gives

"=R[-6]C&"" ""&R[-5]C"

NOTE the double quotes to give a quote within a string (you could also
use chr$(34) to return a double quote)

Mix of relative and absolute?
 

I think this should work for you.

ActiveCell.FormulaR1C1 = "=CONCATENATE(" & Chr$(34) & "TEXT" & Chr$(34)
& ",CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

-I'm working on a macro that has the following line:

ActiveCell.FormulaR1C1 =
"=CONCATENATE(CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

And I want to insert text before the first character return...as in:

ActiveCell.FormulaR1C1 =
"=CONCATENATE("text",CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

Is this possible?
Thanks,
M John-


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=536797

Mix of relative and absolute?
 

Excellent. Glad it was do-able.
Most appreciated.

Thanks,
Mj

" wrote:

Yes, or at least it should be - the formula is simply a string - for
example recording a simple macro that concatenates two cells with a
space between them gives

"=R[-6]C&"" ""&R[-5]C"

NOTE the double quotes to give a quote within a string (you could also
use chr$(34) to return a double quote)

Mix of relative and absolute?
 

Thank you. Excellent work.

MJohn

"Ikaabod" wrote:


I think this should work for you.

ActiveCell.FormulaR1C1 = "=CONCATENATE(" & Chr$(34) & "TEXT" & Chr$(34)
& ",CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

-I'm working on a macro that has the following line:

ActiveCell.FormulaR1C1 =
"=CONCATENATE(CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

And I want to insert text before the first character return...as in:

ActiveCell.FormulaR1C1 =
"=CONCATENATE("text",CHAR(10),RC[-3],CHAR(10),RC[-2],CHAR(10),RC[-1])"

Is this possible?
Thanks,
M John-


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=536797