Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining "Month" as a variable in VBA
Hi All I'm trying to write a macro that will look at 12 monthly sales columns and sum the year to date sales. I think I'm defining the month as a variable with.. Dim Mon As Integer Mon = Month(today) But I get very stange results when I use this to calculate the sales... Range("W1").Select ActiveCell.FormulaR1C1 = "Sales LYTD" With ActiveSheet.Range("W2:W" & LastRow) .FormulaR1C1 = "=SUM(RC[-12]:RC[-" & (12 - Mon) & "])" End With Any idea what I need to change? Thanks Colin (ever amazed by my own lack of knowledge!) -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472 View this thread: http://www.excelforum.com/showthread...hreadid=515414 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining "Month" as a variable in VBA
You have a syntax error
Mon = Month(today) should Mon = Month(Date) that apart, it works fine for me. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Colin Vicary" wrote in message ... Hi All I'm trying to write a macro that will look at 12 monthly sales columns and sum the year to date sales. I think I'm defining the month as a variable with.. Dim Mon As Integer Mon = Month(today) But I get very stange results when I use this to calculate the sales... Range("W1").Select ActiveCell.FormulaR1C1 = "Sales LYTD" With ActiveSheet.Range("W2:W" & LastRow) FormulaR1C1 = "=SUM(RC[-12]:RC[-" & (12 - Mon) & "])" End With Any idea what I need to change? Thanks Colin (ever amazed by my own lack of knowledge!) -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472 View this thread: http://www.excelforum.com/showthread...hreadid=515414 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining "Month" as a variable in VBA
Genius! Thanks Bob 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=515414 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Twist on the variable rate problem | Excel Worksheet Functions | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
object variable or with block variable not set | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
I Need VBA Assistance for global variable question | Excel Discussion (Misc queries) |