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

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

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