Relative references and macro programming
 

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

End Sub

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

Thanks for the help.

Relative references and macro programming
 

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 Whe=xlLocationAsNewSheet
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

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

---
Regards,
Norman


"Ruth" wrote in message
ups.com...
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 Whe=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 Whe=xlLocationAsObject, Name:=s

End Sub

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

Thanks for the help.

Relative references and macro programming
 

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?

Relative references and macro programming
 

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 Whe=xlLocationAsNewSheet
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47934

Relative references and macro programming
 

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!

Relative references and macro programming
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479340

Relative references and macro programming
 

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 Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

--
Regards,
Tom Ogilvy

"Ruth" wrote in message
ups.com...
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!

Relative references and macro programming
 

Hi Leith,

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

ActiveChart.Location Whe=xlLocationAsNewSheet

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

--
Regards,
Tom Ogilvy

"Leith Ross" wrote
in message ...

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479340

Relative references and macro programming
 

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