## ActiveChart.SetSourceData Source with variable data range

## ActiveChart.SetSourceData Source with variable data range

(OP)

Hiya.

I have a spreadsheet which produces a pretty chart based on a bunch of data thrown at it from Access.

The number of rows of data the chart needs to show varies, according to the selected output from Access. At the moment, with a fixed data range, the legend shows symbols for a full dozen rows, which is needed occasionally, but the number varies with every report.

I have a couple of macros that need to be run over the data to set it up to graph, one of which already uses a number of Activechart options to customise the look of the graph. I have found a little bit of info on ActiveChart.SetSourceData Source:=, but I'm having trouble getting the syntax right.

- Cell C6 counts the number of records involved in this particular report.

- The fixed data range is currently ='DIAMETER REPORT'!$A$42:$M$193. Data is arranged in columns, so it's the column range I need to adjust – the row range stays constant.

So I think I need something like:

ActiveChart.SetSourceData Source:=Sheets("DIAMETER REPORT").Range("A42:" & Offset("A42", 152, "c6", 0, 0))

The macro is throwing up a syntax error at the offset though.

I'm not familiar with VBA and am piecing this together through Google searches and deconstructing a couple of macros a co-worker built years ago, so I'd really appreciate any help you can offer to get this going.

I have a spreadsheet which produces a pretty chart based on a bunch of data thrown at it from Access.

The number of rows of data the chart needs to show varies, according to the selected output from Access. At the moment, with a fixed data range, the legend shows symbols for a full dozen rows, which is needed occasionally, but the number varies with every report.

I have a couple of macros that need to be run over the data to set it up to graph, one of which already uses a number of Activechart options to customise the look of the graph. I have found a little bit of info on ActiveChart.SetSourceData Source:=, but I'm having trouble getting the syntax right.

- Cell C6 counts the number of records involved in this particular report.

- The fixed data range is currently ='DIAMETER REPORT'!$A$42:$M$193. Data is arranged in columns, so it's the column range I need to adjust – the row range stays constant.

So I think I need something like:

ActiveChart.SetSourceData Source:=Sheets("DIAMETER REPORT").Range("A42:" & Offset("A42", 152, "c6", 0, 0))

The macro is throwing up a syntax error at the offset though.

I'm not familiar with VBA and am piecing this together through Google searches and deconstructing a couple of macros a co-worker built years ago, so I'd really appreciate any help you can offer to get this going.

## RE: ActiveChart.SetSourceData Source with variable data range

## CODE

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: ActiveChart.SetSourceData Source with variable data range

Cell C6 counts the number of recordsIn wich sheet ?

Hope This Helps, PH.

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

FAQ181-2886: How can I maximize my chances of getting an answer?

## RE: ActiveChart.SetSourceData Source with variable data range

Thanks Skip - not quite what I need though (but I'm copying that syntax down for future use - thank you).

Maybe an example to make it a bit clearer:

Current value of C6 = 2 (2 data sets to graph)

Start of range = A42

Data is arranged in columns, and is always 152 rows long - it's the number of columns that varies.

So, for this example, I need my data range to become A42:C193 = 3 columns wide (the first is the X axis values), 152 rows long.

My code as it stands, without the line I need to insert:

Sub chartadjustment()

ActiveSheet.ChartObjects("Chart 11").Activate

ActiveChart.Axes(xlCategory).Select

With ActiveChart.Axes(xlCategory)

.MinimumScale = Range("c3")

.MaximumScale = Range("c4")

.MinorUnitIsAuto = True

.MajorUnit = 14

.Crosses = xlAutomatic

.ReversePlotOrder = False

.ScaleType = xlLinear

End With

ActiveChart.Axes(xlCategory).HasTitle = True

ActiveChart.Axes(xlCategory).AxisTitle.Text = "Date"

ActiveChart.Axes(xlValue).HasTitle = True

ActiveChart.Axes(xlValue).AxisTitle.Text = Range("c5")

End Sub

## RE: ActiveChart.SetSourceData Source with variable data range

## CODE

Source:=Sheets("DIAMETER REPORT").Range(Cells(42, "A"), Cells(193, [c6] + 1))

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: ActiveChart.SetSourceData Source with variable data range

Run-time Error: 1004

Method 'Cells'of object '_Global' failed

## RE: ActiveChart.SetSourceData Source with variable data range

Maybe it makes more sense to have it earlier in the macro?

## RE: ActiveChart.SetSourceData Source with variable data range

ActiveChart.SetSourceData _

Source:=.Range(.Cells(42, "A"), .Cells(193, .Range("C6") + 1))

End With

Hope This Helps, PH.

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?

FAQ181-2886: How can I maximize my chances of getting an answer?

## RE: ActiveChart.SetSourceData Source with variable data range

It didn't work initially, because it didn't like the "A" notation in the first Cells(), but since A is a constant I just changed that to '1' and it worked.

Complete code is now:

Sub chartadjustment()

ActiveSheet.ChartObjects("Chart 11").Activate

ActiveChart.Axes(xlCategory).Select

With ActiveChart.Axes(xlCategory)

.MinimumScale = Range("c3")

.MaximumScale = Range("c4")

.MinorUnitIsAuto = True

.MajorUnit = 14

.Crosses = xlAutomatic

.ReversePlotOrder = False

.ScaleType = xlLinear

End With

ActiveChart.Axes(xlCategory).HasTitle = True

ActiveChart.Axes(xlCategory).AxisTitle.Text = "Date"

ActiveChart.Axes(xlValue).HasTitle = True

ActiveChart.Axes(xlValue).AxisTitle.Text = Range("c5")

With Sheets("DIAMETER REPORT")

ActiveChart.SetSourceData Source:=.Range(.Cells(42, 1), .Cells(193, .Range("C6") + 1))

End With

End sub

In short, it's main functions are to:

1 adjust the scale of the x axis based on cells where I have calculated the minimum and maximum of my data range.

2 Set the X and Y axis titles, where the Y axis is variable.

3 Adjust the number of columns of data it charts, depending on a count of the number of columns of data in Cell C6.

Thank you again!

## RE: ActiveChart.SetSourceData Source with variable data range

"It didn't work initially, because it didn't like the "A" notation in the first Cells(), but since A is a constant I just changed that to '1' and it worked. "

"A" and A are two DIFFERENT things.

"A" is NOT a constant - it's a LITERAL

A might be a constant that may or may not be equal to "A".

Not matter what the value of the constant A is, "A" is "A" and works ALL the time in...

## CODE

ActiveChart.SetSourceData Source:=.Range(.Cells(42, "A"), .Cells(193, .Range("C6") + 1))

End With

Skip,

_{Just traded in my old subtlety...for a NUANCE!}## RE: ActiveChart.SetSourceData Source with variable data range

## RE: ActiveChart.SetSourceData Source with variable data range

Skip,

_{Just traded in my old subtlety...for a NUANCE!}