TheAntiGates
03-31-2015, 08:05 AM
I have a chart whose X is MySheet!B4:B40 and the plotted values are from MySheet!D4:40. I want to select the X values and essentially go Control-Shift-Down and accordingly expand the range for both X and the series (source data).
Below is what I have. You can walk the code and see what it does, and I think it's interesting, 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)"
(The board seems to be inserting a blank making 40 look like 4 0, sorry about that.)
I want to change both 40's to another value from Range(blah, blah.End(xlDown)).Select
That is, if i had B4 to B40 selected, and went Control-Shift-Down, which then gave B4 to B50, is there a dynamic way for me to expand the source data (series) range, or do I need to string-parse .Formula with Instr? (And better yet, especially for you 'c' programmers, there's a sweeeet strtok() type function, split() !)
function 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 'yeah, yeah, use a With
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 function
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
Below is what I have. You can walk the code and see what it does, and I think it's interesting, 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)"
(The board seems to be inserting a blank making 40 look like 4 0, sorry about that.)
I want to change both 40's to another value from Range(blah, blah.End(xlDown)).Select
That is, if i had B4 to B40 selected, and went Control-Shift-Down, which then gave B4 to B50, is there a dynamic way for me to expand the source data (series) range, or do I need to string-parse .Formula with Instr? (And better yet, especially for you 'c' programmers, there's a sweeeet strtok() type function, split() !)
function 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 'yeah, yeah, use a With
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 function
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