Relative reference in macro

[Ricki Miles]

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

 

[Rob van Gelder]

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

 

[Ricki Miles]

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

 

[Tim Williams]

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 Where:=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 Where:=xlLocationAsObject, Name:=s

End Sub


Tim.