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? 
sub foo
    Dim obj As Object, SeriesXVals As Variant, sStr As String, sResp As String, i As Integer

    For Each obj In Sheets
       If obj.Type = xlChart Then 'a chart sheet
          For i = 1 To obj.SeriesCollection.Count
              SeriesXVals = obj.SeriesCollection(i).XValues
              sStr = "Chart series " & i & " on '" & obj.Name & "' measures values for [" & SeriesXVals(i) & "](as date " & Format(SeriesXVals(i), "m/d/yy") & ") to [" & SeriesXVals(UBound(SeriesXVals)) & "](as date " & Format(SeriesXVals(UBound(SeriesXVals)), "m/d/yy") & "). Okay?" _
              & vbCrLf _
              & vbCrLf & "Yes: proceed to next chart series on this sheet if any, or go to next sheet" _
              & vbCrLf & "No: Interactively modify the range" _
              & vbCrLf & "Cancel: Stop checking charts"
              sResp = MsgBox(sStr, vbYesNoCancel + vbDefaultButton2)
              If sResp = vbCancel Then foo = False: Exit Function
              If sResp <> vbYes Then Call ModifyChartRangesThisSheet(i, SeriesXVals, obj.SeriesCollection(i)):obj.activate
          Next i
       End if	
    Next obj
end sub
Sub ModifyChartRangesThisSheet(i As Integer, varXVals As Variant, myItem As Variant)
    'extend range
    Dim sStr As String
    sStr = InputBox("Right now, range is " & varXVals(i) & " to " & varXVals(UBound(varXVals)) _
           & vbCrLf _
           & vbCrLf & "Now instruct to fill it out by typing exactly:" _
           & vbCrLf & "D for down" _
           & vbCrLf & "R for to right" _
           & vbCrLf & "U for up" _
           & vbCrLf & "L for to left", _
           , "D")
    Select Case sStr
        ' don't need to save my place; go back to obj chart sheet on return
        ' parse MyItem.formula="=SERIES("SomeName",'MySheet'!$B$4:$B$40,'MySheet'!$D$4:$D$40,1)}" 'Note, maybe no single quotes
        ' select the first (B) range and extend it below, say to 41 instead of 40
        ' giving new MyItem.formula="=SERIES("SomeName",'MySheet'!$B$4:$B$41,'MySheet'!$D$4:$D$41,1)}"
        
        Case "D": Range(blah, blah.End(xlDown)).Select
        Case "R": Range(blah, blah.End(xlToRight)).Select
        Case "U": Range(blah, blah.End(xlUp)).Select
        Case "L": Range(blah, blah.End(xlToLeft)).Select
        Case Else: MsgBox "I said EXACTLY. Now you get nothing.":exit sub
    End Select
    'update MyItem.formula now

    'Consider prompting with the new range in InputBox, and let them tweak, or escape to abort

End Sub