Want to change Chart sheet's series cell range

I have a chart whose X is MySheet!B4:B40 and the plotted values are from MySheet!D4:D40. I want to select the X values and essentially go Control-Shift-Down and set that as the new range for both X and the series (source data). Here's what I have. For your simplicity just assume i=1.

You can walk the code and see what it does, but in a nutshell, where I'm stuck is in my aim to efficiently parse the source data formula, e.g.
MyItem.formula="=SERIES("SomeName",'MySheet'!$B$4:$B$40,'MySheet'!$D$4:$D$40,1)}"
and change both 40's to another value from Range(blah, blah.End(xlDown)).Select

Should I just Instr() through the commas in the .formula or is there some other way?

Please Login or Register  to view this content.