Select X axis with macro charting

[bkelly]

Late edit: After reading this post I realized that it appears that I am blaming Jon's code for my errors. The contex in which I used his code is different from that in which he wrote it. I don't really understand the nuances of most of the Excel extensions of VBA and that lack of understanding is the cause of the problem, not Jon's code.
bkelly

From this page on Jon Peltier’s web site:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

I found some example code to help with some macros that I use to create charts. Almost all the time, the macros I wrote (with much help from people in this forum) work just fine. But with some data sets, using the same code, Excel wants to plot the X data on the Y axis and this screws up the entire chart. The core of my charting macros is:

ActiveChart.SetSourceData source:=Charting_Range, PlotBy:=xlColumns

Charting_Range is a union of ranges, each of which represents an entire column. The left most column always becomes the X axis. I don’t specify that, it just seems to work that way. As I understand Jon’s web pages, these following snipits should work.

These two lines of code, tried one at a time:

ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829")
ActiveChart.SeriesCollection(1).XValues = Range(Charting_Range)

And these two lines tried together

'ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(6).XValues = "=data!R1C3:R2829C4"

Both produce the following error.
Run Time Error: :1004’: Method ‘Range’ of object ‘_Global’ failed

The following lines:

With ActiveChart.SeriesCollection.NewSeries
   .Name = ActiveSheet.Range("data")
   .Values = ActiveSheet.Range("D1:D2829")
   .XValues = ActiveSheet.Range("C1:C2829")
End With

Produce this error:
Run-time error ‘438’:
Object doesn’t support this property or method

In all of these above snipits, the X axis should be from column C. The variable Charting_Range is set to the entire column. The range specifier C1:C2829 is intended to represent that same range.

Range specifier R1C2:R38294C4 was intended to contain columns C and D in an attempt to chart column C on the X axis and column D on the Y axis. As noted, it does not work for me.

What can I write that says “Take this range and put its values on the X axis. Then take this other range and put it on the Y axis.”

(edited to correct a typo)

 

[bkelly]

Jon,
After reading your post, I realized that my first post appears to blame your code. I mentioned your web site to direct people there, not to disparage you. I have edited the post to include a note at the top.

I note that your response was nothing but civil and I feel I owe you an appology.

I am working a different set of macros for an immediate need to analyze some data right away but will get back to this problem as soon as I can.

My appologies and thank you for your graceful response to my badly worded post.

 

[Jon Peltier]

Bryan -

After reading your post, I realized that my first post appears to blame your code.

No worries. I hadn't got that impression from reading your initial post. Anyway, communication via internet text is tricky enough, so I try never to assume the worst in someone's intentions.

My thought was that you'd read the article and tried implementing it in a way that stretched beyond your experience (and isn't that how we learn?). So I was trying to address some of the ways I thought might have led to your problems.