Using a variable in a VBA formula
 

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

Using a variable in a VBA formula
 

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

Using a variable in a VBA formula
 

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"
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

Using a variable in a VBA formula
 

Thanks Bob, that's perfect

Colin


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