Colin,

You have to separate the variable from the text

ActiveCell.FormulaR1C1 = "=SUM(RC[" & (Mon-12) & "]:RC[-5])"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Colin Vicary"
wrote in message
news:Colin.Vicary.1y68ry_1131442201.4504@excelforu m-nospam.com...

Hi all

I have a worksheet tht contains year to date sales and sales for last
year. I want to show those sales as "last year to date". This means I
have to know how far through the year we are!

I've set a variable to get that, but now I want to add it to the
formula in the macro.

I've tried this but it's wrong, can anyone tell me how to make it
right?

Thanks

Colin

Dim Mon As Integer
Mon = Month(today) - 1
ActiveCell.FormulaR1C1 = "=SUM(RC[(Mon-12)]:RC[-5])"


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile:
http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=483067