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

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

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