Setting Source Data for Chart Ranges in VB

[Guest]

I want to create a simple XY chart in VB, but I need to have control over
where the data to be charted comes from. Given I want to Chart the range
from Cells(StartRow,StartCol) to Cells(EndRow, EndCol). I can select the
right range to chart, but when it goes to chart it, it creates the chart as 2
series rather than the X and Y for a single series. I follow the recorded
macro format just replacing where the range is inputted with my settings, but
no matter how I set it up, it doesn't seem to work. Ideally, I could set
Xvalues and YValues separately so they don't have to be in columns next to
each other, but I can work around that if need be.

How do I do the SetSourceData Source:=??
Thanks.

 

[Jon Peltier]

In most charts, with most data, Excel will create one series per column. In
an XY chart, or if the first column is all text, or formatted as a date, the
first column is likely to be used as X values or category labels. You can
improve Excel's parsing of the data range if you put header labels in the
row above the data for each set of Y values, and leave the cell above the X
values blank. Include this row of one blank plus labels in the Source:=
range. The blank cell tells Excel that the first row and first column are
special, so the first row becomes the series names and the first column
becomes the X values.

- Jon