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:
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:
And these two lines tried together
Both produce the following error.
Run Time Error: :1004’: Method ‘Range’ of object ‘_Global’ failed
The following lines:
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
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:
Code:
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:
Code:
ActiveChart.SeriesCollection(1).XValues = Range("B1:B2829")
ActiveChart.SeriesCollection(1).XValues = Range(Charting_Range)
And these two lines tried together
Code:
'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:
Code:
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)