Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative reference in macro
I am recording a macro in Excel XP that will select a range of data and make
a chart. The data will always be ten rows and two columns. Since the data may start in different cells on different sheets, I would like the macro to be relative so that when I click a starting cell and then run the macro, it will select the data starting at the active cell. I have tried recording by first activating the starting cell I would like, then Tools, Macro, Record New Macro, clicking the relative reference button on the macro toolbar, then selecting the range of cells and creating the chart. When I use this macro in any starting cell, it always charts the data by starting at the cell I was at when I recorded the macro. What am I doing wrong? Thanks for the help, Ricki |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative reference in macro
When you start recording the macro, the macro toolbar appears. One of the
buttons on that toolbar is "Relative Reference". It records the macro without fixed cell positions - it's all relative from the activecell. Here is some code for adding a chart to your sheet. Sub test() With ActiveSheet.ChartObjects.Add(100, 100, 500, 250) .Chart.ChartWizard Source:=ActiveCell.Resize(10, 2), _ Gallery:=xlLine, Title:="New Chart" End With End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "Ricki Miles" wrote in message ... I am recording a macro in Excel XP that will select a range of data and make a chart. The data will always be ten rows and two columns. Since the data may start in different cells on different sheets, I would like the macro to be relative so that when I click a starting cell and then run the macro, it will select the data starting at the active cell. I have tried recording by first activating the starting cell I would like, then Tools, Macro, Record New Macro, clicking the relative reference button on the macro toolbar, then selecting the range of cells and creating the chart. When I use this macro in any starting cell, it always charts the data by starting at the cell I was at when I recorded the macro. What am I doing wrong? Thanks for the help, Ricki |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative reference in macro
Hi Rob,
Thanks for the help, but I think I tried that and it didn't work. Here's what I did: Data is currently in A1:B10.. Click on A1. Choose Tools, Macro, Record New Macro, name it, store it, OK. Click on the button so that Relative Referencing is in effect. Hold down Shift key and arrow once across and 9 times down. Press F11 for the shortcut to making a chart sheet. Press Stop Recording. To test the relative referencing, with new data in A20:B30, click on A20. Run the macro. The new chart is created, but the data is coming from A1:B10. Is there something I am doing wrong? I appreciate the help. Thanks, Ricki "Rob van Gelder" wrote in message ... When you start recording the macro, the macro toolbar appears. One of the buttons on that toolbar is "Relative Reference". It records the macro without fixed cell positions - it's all relative from the activecell. Here is some code for adding a chart to your sheet. Sub test() With ActiveSheet.ChartObjects.Add(100, 100, 500, 250) .Chart.ChartWizard Source:=ActiveCell.Resize(10, 2), _ Gallery:=xlLine, Title:="New Chart" End With End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "Ricki Miles" wrote in message ... I am recording a macro in Excel XP that will select a range of data and make a chart. The data will always be ten rows and two columns. Since the data may start in different cells on different sheets, I would like the macro to be relative so that when I click a starting cell and then run the macro, it will select the data starting at the active cell. I have tried recording by first activating the starting cell I would like, then Tools, Macro, Record New Macro, clicking the relative reference button on the macro toolbar, then selecting the range of cells and creating the chart. When I use this macro in any starting cell, it always charts the data by starting at the cell I was at when I recorded the macro. What am I doing wrong? Thanks for the help, Ricki |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative reference in macro
How about showing the generated code?
Here's what I got from a simple test (using relative references as you have): ActiveCell.Range("A1:B17").Select ' recorded correctly Charts.Add ActiveChart.ChartType = xlLineMarkersStacked ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("D9:E25") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" note that the "Source" parameter is not assigned a relative reference and that the chart location will always be "Sheet1" Try something like this: Sub Macro1() Dim r As Range Dim s As String Set r = ActiveCell.Resize(10, 10) s = ActiveCell.Parent.Name Charts.Add ActiveChart.ChartType = xlLineMarkersStacked ActiveChart.SetSourceData Source:=r ActiveChart.Location Whe=xlLocationAsObject, Name:=s End Sub Tim. "Ricki Miles" wrote in message ... I am recording a macro in Excel XP that will select a range of data and make a chart. The data will always be ten rows and two columns. Since the data may start in different cells on different sheets, I would like the macro to be relative so that when I click a starting cell and then run the macro, it will select the data starting at the active cell. I have tried recording by first activating the starting cell I would like, then Tools, Macro, Record New Macro, clicking the relative reference button on the macro toolbar, then selecting the range of cells and creating the chart. When I use this macro in any starting cell, it always charts the data by starting at the cell I was at when I recorded the macro. What am I doing wrong? Thanks for the help, Ricki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative reference? in macro | Excel Worksheet Functions | |||
Using relative reference in macro | Excel Discussion (Misc queries) | |||
Relative Reference in a Macro | Excel Programming | |||
How do I set up a macro using relative cell reference? | Excel Programming | |||
MACRO Relative Reference | Excel Programming |