Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using macro to set chart title with a variable month
I have named two cells as range MONTH and YEAR respectively, so that I can
update my report automatically each month. My macro: ------- Range("C1").Select ActiveCell.FormulaR1C1 = _ "=""Sales Performance (YTD ""&MONTH&"" ""&YEAR&"")""" Range("C2").Select ActiveCell.FormulaR1C1 = _ "=&YEAR-1&"" Actual vs ""&YEAR&"" Budget vs ""&YEAR&"" Actual""" ------- This labels the datasheet (cell C1) as the following formula: ="Sales Performance (YTD "&MONTH&" "&YEAR&")" Which does the trick. However, I want the macro to set the datasheet title not as a formula, but as text, using MONTH and YEAR as fixed constants, so that it reads: Sales Performance (YTD October 2007) I've tried amending the fomula as follows: ------- ActiveCell.FormulaR1C1 = _ "Budgeted Trade Lane Performance (YTD " & Month & " " & Year & ")" ------- but as soon as I type this it conerts MONTH and YEAR to Month and Year, and gives an error when I try to run the macro. What I believe I need to do is to Dim Month as MONTH and Year as YEAR at the start of the macro but I'm not sure how to do this? I also have a second problem. I need to do a similar thing to the titles of Chart1, Chart2 and Chart3. But I can't even figure out how to get the macro to label the chart using a formula, as above. The macro currently reads: ------- ActiveChart.ChartTitle.Select Selection.Characters.Text = _ "Sales Performance (YTD October 2007)" & Chr(10) & "2006 Actual vs 2007 Budget vs 2007 Actual" ------- I've tried replicating the above formula in the same manner, but this just enters the whole formula as-is, without calling the MONTH and YEAR ranges. I assume if I can fix the first problem, I can fix the second in the same way? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using macro to set chart title with a variable month
Bit more info, have already tried adding the following:
Const Month As String = "MONTH" Const Year As String = "YEAR" which changes the title to ....YTD MONTH YEAR and Const Month As String = MONTH Const Year As String = YEAR which fails |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using macro to set chart title with a variable month
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can chart title be linked to variable cell address? | Excel Discussion (Misc queries) | |||
can variable be used in chart title | Charts and Charting in Excel | |||
How do I identify Filter criteria or variable graph title? | Excel Discussion (Misc queries) | |||
Pasting Objects into Chart title and Axis title | Charts and Charting in Excel | |||
Pivot Chart Title variable | Charts and Charting in Excel |