I'm trying to create charts in Excel from a bunch of non-contiguous cells
using VBA.

The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$ C9:C10, etc.

I first tried to set Series.Formula with a string that I generated from the
sheet & cell names, but I had enough cells that I hit that 250 character
limit in the Series.Formula arguments.

I tried a number of other tactics, including:
Trying SeriesCollection.Extend (didn't work, since I'm trying to put
data from 1 sheet into the chart)
Trying to just set Series.Values & Series.XValues separately
Applying a name to the renge I wanted to plot, & feeding that into .
Formula, then .Values
(I can't remember what else)

After all this, I found out something absolutely maddening

So maddening indeed that I may be without teeth & hair by the end of the day.

I can:
1 select the series manually with the mouse, and select the "Source
Data" context menu
2 type "=Sheet!NamedRange" into the Values field & hit OK
3 see that the plot has the right data
4 manually select the series again & copy the text of the series
fomula from the formula bar
5 paste said text into VBA code that sets Series.Formula
6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!!

Richo.Breathe.Value = xlSlowly
Richo.Wait(10)

Anyway, does anyone know why this doesn't work? Am I doing something wrong,
or do multi-area ranges somehow work in Excel, but not in VBA?

Obviously I can always make a new sheet specifically for the chart data, but
that somehow seems inelegant...

Any help / kind words are appreciated,

Richo