ActiveChart.SetSourceData Source with variable data range 1

[ulteriormotif]

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.

 

[SkipVought]

ActiveChart.SetSourceData Source:=Sheets("DIAMETER REPORT").Range("A42:A" & [c6] )

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Cell C6 counts the number of records
In wich sheet ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ulteriormotif]

PHV - in the same sheet - "DIAMETER REPORT"

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ulteriormotif]

Skip, that looks good to me, but throws an error:

Run-time Error: 1004
Method 'Cells'of object '_Global' failed

 

[ulteriormotif]

Is it a question of where in the macro I'm putting the line? To test the above I put it directly above the 'end sub' line.

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

 

[PHV]

With Sheets("DIAMETER REPORT")
ActiveChart.SetSourceData _
Source:=.Range(.Cells(42, "A"), .Cells(193, .Range("C6") + 1))
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ulteriormotif]

PHV, yer wunnerful!

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!

 

[SkipVought]

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

With Sheets("DIAMETER REPORT")
  ActiveChart.SetSourceData Source:=.Range(.Cells(42, [b]"A"[/b]), .Cells(193, .Range("C6") + 1))
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ulteriormotif]

*LOL* ok, well now I'm thoroughly confused... but I will make the effort to dissect what you're saying until I get it (the terminology is all new to me so I have to go very step by step).

 

[SkipVought]

The "terminology" is defined in HELP. Check CONSTANT, VARIA BLE, LITERAL

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]