Hello everyone,

I am trying to implement a bit of code that adds a very hairy concatenate formula to a range of cells in a column, but running into a bunch of issues with quotation marks in VBA. The normal formula is as follows:

=CONCATENATE("Print ""<val>",G5,",",F5,",","""",";",$L$5,"\",A5,IF(L5=A5,"","\" & B5),"\",C5,";""</val>""")

Normally, I would try to do something utilizing R1C1 notation to update the relative references of the cells (above, you see that everything is looking at Row 5). I'm trying to manage all of the quotation marks floating around that need to show up in the final string, so here is my following code that is producing errors.

Range("W5:W100000").FormulaR1C1 = _
"=CONCATENATE(""Print ""<val>"",RC[-16],"","",RC[-17],"","","""""","";"",R5C12,""\"",RC[-22],IF(RC[-11]=RC[-22],"""",""\"" & RC[-21]),""\"",RC[-20],"";""</val>"""")"
Perhaps I am going about a very poor way of doing this? Maybe there is a better alternative to R1C1 notation for this scenario? Does anyone have any suggestions for handling long strings like this and accounting for the quotation marks that should be included in the final string?

Thank you