Adding VBA Variables to an equation

Analyze_This

Board Regular
Joined
Oct 29, 2008
Messages
122
I'm writing some code where I take the value below the active cell and concatinate the Month-Year. I recorded this method:

Code:
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:

Code:
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?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Oct 29, 2008
Messages
122
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top