PDA

View Full Version : Dynamically modify Chart source data?



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

Bob Phillips
04-01-2015, 02:27 AM
Can you post the workbook save us trying to recreate.

TheAntiGates
04-06-2015, 03:12 PM
Can you post the workbook save us trying to recreate.MySheet!B4:B50 contain any numbers you want. MySheet!D4:50 contain any numbers you want. Select B4:D40. Hit F11.

The objective is to achieve the effect (on MySheet) of going Ctrl-alt-down so that B4:D50 is selected.

The code probably pretty much does everything up to some InStr and Splits and revising chart properties to use source data B4:D50 instead of B4:D40.

TheAntiGates
04-06-2015, 03:17 PM
I don't see right away how to turn off icon substitution but what above is trying to say, with extra spaces below to defeat the substitution, is
B4 : D50
and
B4 : D40

A colon and D seem to create a joyous smiley.

mperrah
04-06-2015, 04:21 PM
I have used dynamically naming row and call the named range in the code for the chart

=OFFSET(QCDetail!$I$2,0,0,COUNTA(QCDetail!$C:$C)-1)
this is the named range for the first item of my chart. by using the offset and CountA, what ever is in the row can be used.

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,INDIRECT(maChoice),0))))

I use sumproduct to choose what gets displayed on the chart. It acts like a filter for multiple matches.
I use drop down validation cells for date range (start and Stop) and the data gets pulled dynamically based on the contents of the dynamic range.
My data flows from top to bottom based on date, the each column holds different elements (item01 to item26) for the chart

there is a sticky note on sumproduct in this forum, thats where I sourced the info .
13125

TheAntiGates
04-07-2015, 10:11 AM
=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,INDIRECT(maChoice),0))))Ah, using COUNTA for the number of elements, nice. That will often work, though sometimes there's more noise in the column or row of concern. But now, using the number of rows and column args to the OFFSET function, that's just an awesome idea. I always forget those other arguments are available.
I use sumproduct to choose what gets displayed on the chart. It acts like a filter for multiple matches.I love it! That's going in.

I'll go ahead and mark this solved because it doesn't look like I'm going to get any response on using the properties in VBA. Thanks for the fine ideas and cool explanations!