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

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)

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

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

Thanks,
Mj

"aidan.heritage@virgin.net" 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)
>
>

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
>
>