Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ActiveChart.SetSourceData Source with variable data rangeHelpful Member! 

ulteriormotif (TechnicalUser)
19 Nov 08 21:26
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 (Programmer)
20 Nov 08 6:37


CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Helpful Member!  PHV (MIS)
20 Nov 08 12:39
ulteriormotif (TechnicalUser)
20 Nov 08 14:39
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 (Programmer)
20 Nov 08 21:31

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

ulteriormotif (TechnicalUser)
20 Nov 08 21:46
Skip, that looks good to me, but throws an error:

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

 
ulteriormotif (TechnicalUser)
20 Nov 08 21:51
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 (MIS)
21 Nov 08 5:01
With Sheets("DIAMETER REPORT")
  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?

ulteriormotif (TechnicalUser)
23 Nov 08 14:49
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 (Programmer)
23 Nov 08 16:52

"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

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

ulteriormotif (TechnicalUser)
23 Nov 08 17:49
*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 (Programmer)
23 Nov 08 18:00
The "terminology" is defined in HELP. Check CONSTANT, VARIA BLE, LITERAL

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close