Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: Graph macro using reletive reference?
Hi,
I recorded the following macro: Sub Past_month_click() ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=Range("B50:H54") End Sub What do I want to accomplish? I have a range of data per month. When I do my thing in the sheet I want to add the last month to my graph. In Excel I have to right click the graph, change the data range, press the right key, followed by pressing the Enter key. This I would like to do by the macro. So, the Range("B50:H54") must become a relative reference. What do I need to change in the above code to accomplish that? Abel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: Graph macro using reletive reference?
Something like this
Sub Past_month_click() LastRow = 15 Set MyRange = Range("B1:B" & LastRow) ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=MyRange End Sub "Abel MacAdam" wrote: Hi, I recorded the following macro: Sub Past_month_click() ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=Range("B50:H54") End Sub What do I want to accomplish? I have a range of data per month. When I do my thing in the sheet I want to add the last month to my graph. In Excel I have to right click the graph, change the data range, press the right key, followed by pressing the Enter key. This I would like to do by the macro. So, the Range("B50:H54") must become a relative reference. What do I need to change in the above code to accomplish that? Abel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: Graph macro using reletive reference?
Hi Joel,
Thanks for helping me. Sadly, no. The graph is wiped clean. The range it uses for its data is altered to a location containing no data. Even a 'Undo' did not get me my data back. Abel "Joel" wrote: Something like this Sub Past_month_click() LastRow = 15 Set MyRange = Range("B1:B" & LastRow) ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=MyRange End Sub "Abel MacAdam" wrote: Hi, I recorded the following macro: Sub Past_month_click() ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=Range("B50:H54") End Sub What do I want to accomplish? I have a range of data per month. When I do my thing in the sheet I want to add the last month to my graph. In Excel I have to right click the graph, change the data range, press the right key, followed by pressing the Enter key. This I would like to do by the macro. So, the Range("B50:H54") must become a relative reference. What do I need to change in the above code to accomplish that? Abel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: Graph macro using reletive reference?
I have been thinking. I have the following table (points are used to get
everything lined out nicely): ..............Jan.......Feb.......Mrt.......Apr... ....Mei.......Jun.......Jul Totaal....127.......148.......152.......153....... 129.......100.......112 Spoed......22........26........37........27....... .14........13........14 Normaal..100.......109.......109.......118.......1 09........80........90 Changes....5........13.........6.........8........ .6.........7.........8 The graph contains data upto and including the Jun column. The macro needs to expand the data range with the Jul column. I recorded a macro using relative references, executing the following steps: 1. Right click the graph, choose 'Select datarange...' (need to translate, I use the Dutch version); 2. Choose the Datarange of Graph (above text box in the window) 3. Go to the field containing 'Changes' (bottom left of my table); 4. Press Shift and End together, followed by pressing the right key (the row from changes to the cell off the Jul column is now selected); 5. Press Shift End and Up, followed by an Up (the whole table as depicted above is selected) I got the following code: Sub SelectDataRange() ' ' SelectDataRange ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=ActiveCell.Range("A1:G5") End Sub Will this macro be usable in August, September, and so on? And if not, what do I need to use to get what I described in my five steps? Will a named cell (cell containing 'Changes' gets the name 'Anchor') be usable? Abel "Abel MacAdam" wrote: Hi Joel, Thanks for helping me. Sadly, no. The graph is wiped clean. The range it uses for its data is altered to a location containing no data. Even a 'Undo' did not get me my data back. Abel "Joel" wrote: Something like this Sub Past_month_click() LastRow = 15 Set MyRange = Range("B1:B" & LastRow) ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=MyRange End Sub "Abel MacAdam" wrote: Hi, I recorded the following macro: Sub Past_month_click() ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=Range("B50:H54") End Sub What do I want to accomplish? I have a range of data per month. When I do my thing in the sheet I want to add the last month to my graph. In Excel I have to right click the graph, change the data range, press the right key, followed by pressing the Enter key. This I would like to do by the macro. So, the Range("B50:H54") must become a relative reference. What do I need to change in the above code to accomplish that? Abel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007: Graph macro using reletive reference?
I just gave an example of how to change the code. I didn't know how your
range was changing dynamically. You had a range of B50:H54 and my code only had B1:B15. The chart object on still on the worksheet. It didn't get deleted. There is just no data to fill the chart so it became invisible. "Abel MacAdam" wrote: Hi Joel, Thanks for helping me. Sadly, no. The graph is wiped clean. The range it uses for its data is altered to a location containing no data. Even a 'Undo' did not get me my data back. Abel "Joel" wrote: Something like this Sub Past_month_click() LastRow = 15 Set MyRange = Range("B1:B" & LastRow) ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=MyRange End Sub "Abel MacAdam" wrote: Hi, I recorded the following macro: Sub Past_month_click() ' ActiveSheet.ChartObjects("Graph 15").Activate ActiveSheet.ChartObjects("Graph 15").Activate ActiveChart.SetSourceData Source:=Range("B50:H54") End Sub What do I want to accomplish? I have a range of data per month. When I do my thing in the sheet I want to add the last month to my graph. In Excel I have to right click the graph, change the data range, press the right key, followed by pressing the Enter key. This I would like to do by the macro. So, the Range("B50:H54") must become a relative reference. What do I need to change in the above code to accomplish that? Abel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Cell Reference - Excel 2007 | Excel Discussion (Misc queries) | |||
Reletive Referencing - Marco issues | Excel Programming | |||
Transpose Reletive Cell References? | Excel Worksheet Functions | |||
paste reletive reference in pivot table | Excel Discussion (Misc queries) | |||
Reletive Referencing of Another Cell's Formulas | Excel Programming |