Replacing placeholder letters with formulas in a macro

Hello, I am very much new to macros. I recorded the following macro:

Please Login or Register  to view this content.

In the activecell.formula part, I want to replace my placeholder formula (=R[-6]C) with a different formula:

=(TEXTJOIN("",TRUE,IFERROR((MID(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH("*"&$B9&"*",INDIRECT($C9&"!$1:$1"),0)),ROW(INDIRECT("1:"&LEN(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH($B9,INDIRECT($C9&"!$1:$1"),0))))),1)*1),""))/100)*VLOOKUP(LEFT(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH($B9,INDIRECT($C9&"!$1:$1"),0)),3)&"/EUR",'Currency Conversion Rates'!$A:$B,2,FALSE)

When I directly paste it in, I get an error. I'm assuming it's something to do with the R1C1 format, but I'm not sure how to change this (when typing in my formula, I typed it in as =AZ4 - not sure why it switched to RC format).

How can I fix this?

Thanks a lot

Open the file.
Write desired formula in AZ9.
Make sure AZ9 is selected (after writing formula an accepting with e+Enter, probably AZ10 will be active so move one cell up).
Open VBE (Ctrl+F11) and in immediate window (if you don't see it use Ctrl+G) write:

Please Login or Register  to view this content.

you will get the formula written in desired format


You may also use just .formula not .formula r1c1

note that any double quote in formula text shall be doubled so try (no sample file so not checked it out):

Please Login or Register  to view this content.

PS. Please edit your post and use CODE tags around your code

Originally Posted by hotdeaths

In the activecell.formula part, I want to replace my placeholder formula (=R[-6]C) with a different formula:

=(TEXTJOIN("",TRUE,IFERROR((MID(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH("*"&$B9&"*",INDIRECT($C9&"!$1:$1"),0)),ROW(INDIRECT("1:"&LEN(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH($B9,INDIRECT($C9&"!$1:$1"),0))))),1)*1),""))/100)*VLOOKUP(LEFT(INDEX(INDIRECT($C9&"!$A:$R"),MATCH(INDEX('Role Convertor'!$C:$C,MATCH(AZ$6&AZ$8,'Role Convertor'!$A:$A&'Role Convertor'!$B:$B,0)),INDIRECT($C9&"!$A:$A"),0),MATCH($B9,INDIRECT($C9&"!$1:$1"),0)),3)&"/EUR",'Currency Conversion Rates'!$A:$B,2,FALSE)

Untested,
Try change to

Please Login or Register  to view this content.