Updating Pie Charts Series Values with unpredictable source data

[MLPaletts]

Hello,

I'm relatively new to VBA and updating pie charts has proven to be a big challenge.

The monthly report I create consists of approximately 120 pie charts and the number of slices in the charts can change from one month to the next. I refuse to do each one manually.

I have vlookup formulas set up that return volume results used in pie charts. I currently have the formulas set up to return a "blank" if the result is an error. My problem is I can't figure out how to find the last cell with data.

Code 1 below updates the pie chart, but only if I delete the formulas returning blanks.

Code 2 finds the last cell with data. The issue with Code 2 is I can't figure out how to implement it with Code 1. Code 3 below is my attempt to get this to work, but experienced coders will probably get a chuckle.

Please help. We are on a tight timeline and the deliverable is drawing near.

Thank you in advance





Code 1
Sub AGraphSeries2a()
ActiveSheet.ChartObjects("Chart 6").Activate
With Sheets("LVL1 GRAPH DATA Site")
ActiveChart.SeriesCollection(1).Values = _
.Range("G46:G" & .Range("G59" & ActiveCell).End(xlUp).Row)
End With
End Sub

Code 2
Range("G46:G60").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select


Code 3
Sub Find()
'
Dim st As Long
Dim ed As Long
Dim sh As Worksheet
Dim ws As Worksheet

Set sh = Sheets("LVL1 GRAPH DATA Site")
Set ws = Sheets("TBC Phone Graphs Roll _Temp")


st = sh.Range("G46").Activate
ed = Sheets("LVL1 GRAPH DATA Site").Range("G46:G60").Activate
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select

With sh

Range(("G46" & ":G" & 46), ("G" & ed & ":G" & ed)).Activate
End With


End Sub

[Bmoe]

I'm only having a chuckle because I'm in a similar situation. We've got data everywhere and I find that manually summarizing it is a waste of time and have had to do exercises like this to make heads or tails of it.

Where is the source data and where is the data that feeds the charts? I'm not sure if I understand, can you post a sample or something?

[MLPaletts]

BMoe,

Thank you for your reply.

In the attached workbook I'm trying to update Chart 6 on the Phone Graphs Roll tab with data from LVL1 GRAPH DATA tab. The main issue causing the problems is finding the last cell need for the series values. The number of metrics that can return on a any given month can be more or less than the previous.

Thank you for your help.
Michael

[Bmoe]

You should be able to use something like

Please Login or Register  to view this content.

If all the data starts in the same place you should be able to use this to find the bottom of a range if there are no blanks.

If you have a sheet that has no data, you will have to trap that out first.

Please Login or Register  to view this content.