PDA

View Full Version : Solved: modifying existing chart Sourch data



figment
06-30-2008, 08:57 AM
i apologize if this question has been asked, for i have not had the time to adequately search this forum, but here is my problem.

i have a chart sheet, and i want the chart to make sure its data is up to date when ever the chart is selected. i have most the code written, except for updating the chart data series. right now the code checks to see if the input data is up to date, and if it is not it adds a row to it till it is. what i don't know how to do is extend the chart serise data to include these new rows.


the code thus far
Private Sub Chart_Activate()
Dim a As Long, ch As Chart, ce As Range
a = Worksheets("MDReturn").Range("A1").End(xlDown).Row
Set ch = Charts("Day to Return Monthly Chart")
While Month(Now()) <> Month(Worksheets("MDReturn").Range("A" & a))
Worksheets("MDReturn").Range("A" & a & ":M" & a).Copy Worksheets("MDReturn").Range("A" & a + 1 & ":M" & a + 1)
For Each ce In Worksheets("MDReturn").Range("A" & a + 1 & ":M" & a + 1)
If IsError(ce) Then
Worksheets("MDReturn").Range("A" & a + 1 & ":M" & a + 1).ClearContents
GoTo noncurrentdata
End If
Next
'section where i need to update the chart data

Wend
noncurrentdata:
End Sub

Bob Phillips
06-30-2008, 09:15 AM
What exactly does that code do (supposed to do)?

figment
06-30-2008, 10:11 AM
i have a worksheet where the chart pulls its information, this sheet has a bunch of formulas that generate the data by month, so the while loop checks to see if the data sheet goes up to the current month, if it doesn't then the while loops till you reaches the correct month. i make the assumption that there will be at least one data point for each month, which may not be true, but has been for the last 4 years, so i will stick with it till it becomes a problem. Now that i think about it i probability need to fill down not copy and paste.

but after it has filled in the required data, i need to adjust the chart source data for the new column lengths. right now the chart uses the A column to as its x axis labels, and the rest of the columns as data series.

hope this explanation helps.

i have reworked what i have, so that it actually works, other then updating the chart

Private Sub Chart_Activate()
Dim a As Long, c As Long, ch As Chart, ce As Range
a = Worksheets("MDReturn").Range("A1").End(xlDown).Row
c = a
Set ch = Charts("Day to Return Monthly Chart")
While Month(Now()) <> Month(Worksheets("MDReturn").Range("A" & c))
Worksheets("MDReturn").Range("A" & c - 1 & ":M" & c).AutoFill Worksheets("MDReturn").Range("A" & c - 1 & ":M" & c + 1), xlFillDefault
Application.Calculate
For Each ce In Worksheets("MDReturn").Range("A" & c + 1 & ":M" & c + 1)
If IsError(ce) Then
Worksheets("MDReturn").Range("A" & c + 1 & ":M" & c + 1).ClearContents
GoTo noncurrentdata
End If
Next
c = c + 1
Wend
noncurrentdata:
If c <> a Then 'adjust chart inputs
ch.SeriesCollection
End If

End Sub

Bob Phillips
06-30-2008, 12:35 PM
Nope, I am still not seeing it. Do you have a workbook to spell it out to me?

figment
06-30-2008, 01:04 PM
unfortunately i cant give you the data that this pulls from, but here is the graph and the sheet it gets its info from. when i select the graph i want it to check the last line of the info page against the month, if they are equal then it dose nothing, otherwise it creates a new line and checks again, it keeps making lines until it has caught up with the current month, or runs across an error. after this i needs to adjust the graph source data, to include these new lines. i am not sure how to alter graph source data.

figment
06-30-2008, 01:25 PM
well i have figured it out, its been a long time sense i had to use record macro, but it still works.


Private Sub Chart_Activate()
Dim a As Long, b As Long, c As Long, ch As Chart, ce As Range
a = Worksheets("MDReturn").Range("A1").End(xlDown).Row
c = a
Set ch = Charts("Submittal Information by Month")
While Month(Now()) <> Month(Worksheets("MDReturn").Range("A" & c))
Worksheets("MDReturn").Range("A" & c - 1 & ":M" & c).AutoFill Worksheets("MDReturn").Range("A" & c - 1 & ":M" & c + 1), xlFillDefault
Application.Calculate
For Each ce In Worksheets("MDReturn").Range("A" & c + 1 & ":M" & c + 1)
If IsError(ce) Then
Worksheets("MDReturn").Range("A" & c + 1 & ":M" & c + 1).ClearContents
GoTo noncurrentdata
End If
Next
c = c + 1
Wend
noncurrentdata:
If c <> a Then 'adjust chart inputs
ch.SeriesCollection(1).XValues = "=MDReturn!$A$2:$A$" & c
For b = 1 To ch.SeriesCollection.Count
ch.SeriesCollection(b).Formula = Replace(ch.SeriesCollection(b).Formula, a, c)
Next
End If
End Sub