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

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

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

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