Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to make a graph: help selecting the data ranges.
Hi everybody,
I need to write a macro that makes a graph. I recorded a macro creating the graph myself with the help of the chart wizard and I understood what code I have to use. But I still have some problems... Each time I use the macro the range of cells to build the graph will be different and I have to make the code select them. I can manage to have the x and y values for the graph always on the same columns (column A for x and B for Y for exemple). I tried to do this: Range("A2").Select Set x_values = ActiveCell.EntireColumn Range("B2").Select Set y_values = ActiveCell.EntireColumn Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = x_values ActiveChart.SeriesCollection(1).Values = y_values With .EntireColumn it works but when the range is small I get a huge x axis scale. Any idea of how I can know what is the highest value in my data for the x axis and set the .MaximumScale to that value? Instead of .EntireColumn I tried also .CurrentRegion and .CurrentArray. The first one returns an error on the last 2 rows of code because my columns of data are not separated by blank columns.The second returns an error that says that no cells were found. I hope I was clear... Any suggestions would be welcomed and I thank you in advance for your help on this subject! Isabel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to make a graph: help selecting the data ranges.
Range("A2").Select
set x_values = Range("A2",Range("A2").End(xldown)) set y_values = x_values.offset(0,1) rest of your code. -- Regards, Tom Ogilvy "Isabel" wrote: Hi everybody, I need to write a macro that makes a graph. I recorded a macro creating the graph myself with the help of the chart wizard and I understood what code I have to use. But I still have some problems... Each time I use the macro the range of cells to build the graph will be different and I have to make the code select them. I can manage to have the x and y values for the graph always on the same columns (column A for x and B for Y for exemple). I tried to do this: Range("A2").Select Set x_values = ActiveCell.EntireColumn Range("B2").Select Set y_values = ActiveCell.EntireColumn Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = x_values ActiveChart.SeriesCollection(1).Values = y_values With .EntireColumn it works but when the range is small I get a huge x axis scale. Any idea of how I can know what is the highest value in my data for the x axis and set the .MaximumScale to that value? Instead of .EntireColumn I tried also .CurrentRegion and .CurrentArray. The first one returns an error on the last 2 rows of code because my columns of data are not separated by blank columns.The second returns an error that says that no cells were found. I hope I was clear... Any suggestions would be welcomed and I thank you in advance for your help on this subject! Isabel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to make a graph: help selecting the data ranges.
Thank you very much, it works perfectly. You made my day!
Isabel "Tom Ogilvy" wrote: Range("A2").Select set x_values = Range("A2",Range("A2").End(xldown)) set y_values = x_values.offset(0,1) rest of your code. -- Regards, Tom Ogilvy "Isabel" wrote: Hi everybody, I need to write a macro that makes a graph. I recorded a macro creating the graph myself with the help of the chart wizard and I understood what code I have to use. But I still have some problems... Each time I use the macro the range of cells to build the graph will be different and I have to make the code select them. I can manage to have the x and y values for the graph always on the same columns (column A for x and B for Y for exemple). I tried to do this: Range("A2").Select Set x_values = ActiveCell.EntireColumn Range("B2").Select Set y_values = ActiveCell.EntireColumn Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = x_values ActiveChart.SeriesCollection(1).Values = y_values With .EntireColumn it works but when the range is small I get a huge x axis scale. Any idea of how I can know what is the highest value in my data for the x axis and set the .MaximumScale to that value? Instead of .EntireColumn I tried also .CurrentRegion and .CurrentArray. The first one returns an error on the last 2 rows of code because my columns of data are not separated by blank columns.The second returns an error that says that no cells were found. I hope I was clear... Any suggestions would be welcomed and I thank you in advance for your help on this subject! Isabel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to change graph ranges? | Excel Discussion (Misc queries) | |||
Selecting Variable Data Ranges | Excel Discussion (Misc queries) | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
graph multiple data ranges | Charts and Charting in Excel |