Mix of relative and absolute?

[Guest]

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

 

[aidan.heritage]

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)

 

[Ikaabod]

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-

 

[Guest]

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

Thanks,
Mj

 

[Guest]

Thank you. Excellent work.

MJohn