Adding VBA Variables to an equation

[Analyze_This]

I'm writing some code where I take the value below the active cell and concatinate the Month-Year. I recorded this method:

ActiveCell.FormulaR1C1 = _
        "=R[-4]C&"" ""&TEXT(R[-8]C[4]&""-""&R[-9]C[4],""mmm-yyyy"")"

And then tried to add in the variables (which I have confirmed do work fine). I've tried several methods with this one getting me the closes:

Do
        ActiveCell.FormulaR1C1 = _
        "=R[1]C&"" ""&TEXT( & Month & "" - "" & year &,""mmm-yyyy"")"
        ActiveCell.Offset(0, 1).Select
    Loop Until ActiveCell.Column = RightStop

Do I just have the Syntax wrong, or am I completely wrong on how this works?

 

[Scott Huish]

First off, you very rarely need to select ranges, you can work with ranges directly in VBA.

What is in the cell below? What are you trying to return exactly? Please explain in words not in formula.

 

[Analyze_This]

Thanks for replying. The document I have comes in sections (4 columns per section) with a merged cell over 4 columns. This merged cell has date values like 201003 (2010 May). I need to concatenated this with the header below the merged cell, but in a format like "header May-2010".

I umnerged all these cells and used the LEFT and RIGHT formula to seperate it out into variables i called Month and Year. So each header should say "Sec Revenue May-2010", "Overall Revenue May-2010", etc for all 4 until it moves into the next section where it will repeat the same process.

Does that make sense?