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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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