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

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
>

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
> >