Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro using relative references | Excel Worksheet Functions | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
macro vba relative references | Excel Worksheet Functions | |||
relative cell references in vba macro | Excel Programming | |||
Making a macro references relative | Excel Programming |