Using a variable in a VBA formula

[Colin Vicary]

Hi all

I have a worksheet tht contains year to date sales and sales for las
year. I want to show those sales as "last year to date". This means
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 th
formula in the macro.

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

Thanks

Colin

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[R.VENKATARAMAN]

if a1 is 11/2/05
and in B1 if you type
=A1-365
you will get
11/2/04
is this what you want

if it is leap year you have to use 366


"Colin Vicary" <[email protected]>
wrote in message
news:[email protected]...

 

[Colin Vicary]

Thanks Bob, that's perfect

Coli