Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Range for a Chart & Trendline
Can anyone help? I have a pivot table of values that I am using in a chart. I
am using a lookup formula to have a range of 100 possible values within the worksheet. Then I run this little "update" macro. I have an dynamic range for the 100 possible values. =OFFSET(Repair Learning Curve Coeff!$G$4,0,0,COUNTA (Repair Learning Curve Coeff!$G$4:$G$103),1) I keep getting an error on the :=ActiveSheet.Range("ChartData") line of 'Runtime error 1004' Application-defined or object-defined error. The range is still showing from G4:G103 (I assume it's from the dynamic range formula?) If I put the range of G4:G103 in the ActiveSheet.Range I get past the error but then I will get a message for the trendline of "Some trendlines cannot be calculated from data containing negative or zero values". Again it's because the range is not changing for only those cells with values. Not sure how to rewrite things. Any ideas? Sub Update() ' Dim NbrRepairs As Range Dim ChartData As String ' copy values outside of pivot to allow forumula's to calculate in learning curve graphs Columns("B:B").Select Selection.Copy Columns("C:C").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("D9").Select Application.CutCopyMode = False ' Uppdate graph Set NbrRepairs = Range("K52") ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("ChartData"), PlotBy: =xlColumns ActiveChart.Axes(xlCategory).Select With ActiveChart.Axes(xlCategory) .MinimumScaleIsAuto = True .MaximumScale = NbrRepairs .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With ' Add Trendline ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPower, Forward:=0, _ Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select ActiveWindow.Visible = False Windows("Repair Manpower 2008 b.xls").Activate Range("V42").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Select Range("C4").Select End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic range and chart - big help!! | Excel Worksheet Functions | |||
Chart empty with new data in the dynamic range chart. | Excel Discussion (Misc queries) | |||
dynamic chart range with VBA | Excel Programming | |||
Dynamic Range Chart ??? | Charts and Charting in Excel | |||
Dynamic Chart Range and Chart Update | Excel Programming |