Using Defined Variables in R1C1 Formula

So this is a pretty basic question I think that I often stumble with:

I have three variables: YY, MM, DD. In one cell I want to insert the letters "DC" and then my 3 variables "YY", "MM", "DD", and then reference the cell 4 columns to the right so RC[4].

With my attempt I get a ?NAME error and it produces the actual letters of "YY", "MM", "DD" instead of the variable assigned to those.

FormulaR1C1 = "="DC" & YY & MM & DD & RC[4]"

What are the variable values and what is the manual entered formula? I usually quote the test formula as a comment before .Formula or .FormulaR1C1 so I can see if I get the string right.

e.g.

Debug.Print Application.ReferenceStyle, [A1].Address, [R1C1].Address, [A1].Address(ReferenceStyle:=xlR1C1)

Originally Posted by Kenneth Hobson

What are the variable values and what is the manual entered formula? I usually quote the test formula as a comment before .Formula or .FormulaR1C1 so I can see if I get the string right.

e.g.

Debug.Print Application.ReferenceStyle, [A1].Address, [R1C1].Address, [A1].Address(ReferenceStyle:=xlR1C1)

The variable values are just the current date. So 17 for YY, 11 for MM, and 16 for DD. The cell 4 columns over is the number 01. So the result should be DC17111601

try this

FormulaR1C1 = "=""DC"" & " & YY & " & " & MM & " & " & DD & " & RC[4]"

it's a bit of a minefield of "'s and &'s