Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use a variable array in a formula | Excel Discussion (Misc queries) | |||
Hide formula | Excel Worksheet Functions | |||
Refrencing a variable cell in a formula | Excel Discussion (Misc queries) | |||
Variable Sheet Name in Formula | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |