Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute & relative references | Excel Discussion (Misc queries) | |||
Relative and absolute hyperlinks | New Users to Excel | |||
Absolute vs Relative path | Excel Discussion (Misc queries) | |||
Absolute / Relative References | Excel Programming | |||
Chg Absolute to Relative | Excel Programming |