Relative references and macro programming

[Ruth]

I am trying to use a macro to make a line graph of certain cells within
my worksheet (Excel 2004:mac). The data will always be 143 rows. I
want to be able to select the starting cell, and have the macro
automatically select the 142 rows below it and create a line graph. I
have tried using relative referencing within my macro, but the graph
that is created always results in my original selected data being
graphed. I made sure relative referencing is selected. What am I
doing wrong?

This is the code that is generated from my macro:

Sub Macro4()
ActiveCell.Range("A1:A143").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("data").Range("G79:G221"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I found an earlier thread that talked about this same situation but I
could not get it to work. This was the code that was suggested:

Sub Macro1()

Dim r As Range
Dim s As String
Set r = ActiveCell.Resize(10,0)
s = ActiveCell.Parent.Name

Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=r
ActiveChart.Location Where:=xlLocationAsObject, Name:=s

End Sub

I got a syntax error when trying this out...

Thanks for the help.

 

[Norman Jones]

Hi Ruth,

Try something like:

'===========>>
Sub Tester()
Dim rng As Range

Set rng = ActiveCell.Resize(143)

Charts.Add

With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=rng, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub
'<<===========

 

[Ruth]

Hi Norman,

Thanks for the help. Unfortunately when I run the macro, I get the
following: Runtime error '424': Object required. When I select
"Debug," the following line is highlighted:

..SetSourceData Source:=rng, PlotBy:=xlColumns

I copied and pasted the macro you descibed...am I missing something
else?

 

[Leith Ross]

Hello Ruth

This should work...


Code
-------------------

Sub Macro4()

ActiveCell.Resize(143, 1).Select

Charts.Add
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Sheets("data").Range("G79:G221"), PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub

 

[Ruth]

Hi Leith,

Thanks for the help! The macro is successful in selecting my desired
cells and creating a graph. But the data range is the same for every
graph (G79:G221). I noticed the code that you provided indicates:

..SetSourceData Source:=Sheets("data").Range("G79:G221"),
PlotBy:=xlColumns

How do you change the code so that the data range is dependent on the
selected cells? I tried to figure something out on my own but was
unsuccessful. I know what to fix here, but I`m new at visual basic
programming so I don`t know how I would fix it.

Thanks again!

 

[Leith Ross]

Hello Ruth,

When I read your post I wasn't really certain if you wanted to choose
the cells for the chart's location or the chart's input data. It sounds
like you want to be able to select the input data range and have the
chart in column "A". Is this right?

Sincerely,
Leith Ross

 

[Tom Ogilvy]

This worked for me:

Sub Macro4()
Dim rng As Range
Worksheets("Data").Select
Set rng = ActiveCell.Resize(143, 1)
Set rng = Intersect(rng.EntireRow, Columns(7))
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=rng, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

 

[Tom Ogilvy]

Hi Leith,

have the
chart in column "A". Is this right?

ActiveChart.Location Where:=xlLocationAsNewSheet

would indicate she wants the chart on a new chart sheet.

 

[Ruth]

Tom-
This works perfectly for me! Thank you so much for you help. It is
greatly appreciated - you just saved me hours and hours of making
charts by hand.

Leith-
As Tom wrote below, I wanted to choose cells from the same worksheet
and have a chart created on another sheet. Thanks for you help as
well!

Sincerely,
Ruth