Cannot change Series references

[Robert Baer]

Sub Macro8()
'
' Macro8 Macro
' Macro does not change Series references
TitleName = "Stripper Well Survey - "
Windows("StripperWellsMod.xls").Activate
Sheets("Charts").Select
Range("A1").Select
SheetColumn = 1
ChartNum = 1
ActiveSheet.ChartObjects("Chart 1").Activate
Sheets("#of wells").Select
RowLoc = LTrim(Str$(43 + ChartNum))
NameLoc = "B" + RowLoc 'eg: B44
Range(NameLoc).Select 'state name, eg: ALABAMA
CTitle = TitleName + ActiveCell.Value
Sheets("Charts").Select
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A1
Range(TextSheetColumn).Select
ChartName = "Chart" + Str$(ChartNum) 'eg: Chart 1
ActiveSheet.ChartObjects(ChartName).Activate
With Worksheets("Charts").ChartObjects(ChartName).Chart
.HasTitle = True
.ChartTitle.Text = CTitle
End With
' Title is selected at this point and is changed
ActiveChart.ChartArea.Copy
SheetColumn = SheetColumn + 21
TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) 'eg: A22
Range(TextSheetColumn).Select
ActiveSheet.Paste
' Now have two identical charts with second one selected
ChartNum = ChartNum + 1
ChartName = "Chart" + Str$(ChartNum) 'eg: Chart 2
ActiveWindow.Visible = False
Selection.Name = ChartName
' Now have copy .. so try changing..
RowLoc = LTrim(Str$(43 + ChartNum))
Series_1 = "='#of wells'!$C$" + RowLoc + ":$BQ$" + RowLoc
Series_2 = "=Production!$C$" + RowLoc + ":$BQ$" + RowLoc
' ActiveChart.SeriesCollection(1).Values = Series_1
' ActiveChart.SeriesCollection(2).Values = Series_2
' Following code stolen from macro recorded when changing Y Values
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "='#of wells'!R45C3:R45C69"
ActiveChart.SeriesCollection(2).Values = "=Production!R45C3:R45C69"
Windows("StripperWellsMod.xls").ScrollRow = 13
ActiveWindow.Visible = False
Windows("StripperWellsMod.xls").Activate
' Above code makes NO changes, so..
' eXplicit values from chart give error "unable to set the Values
property..
' same problem without the $ signs..
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "='#of wells'!$C$45:$BQ$45"
ActiveChart.SeriesCollection(2).Values = "=Production!$C$45:$BQ$45"
Windows("StripperWellsMod.xls").ScrollRow = 13
ActiveWindow.Visible = False
Windows("StripperWellsMod.xls").Activate
'
End Sub

 

[Tushar Mehta]

See the comments to your other similar post. Also, I have no problems with
code like

ActiveChart.SeriesCollection(1).Values = "=charts!$A$1:$A$3"

or

With ActiveSheet.ChartObjects
.Item(.Count).Chart.SeriesCollection(1).Values = "=charts!$L$2:$N$2"
End With

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Robert Baer]

See the comments to your other similar post. Also, I have no problems with
code like

ActiveChart.SeriesCollection(1).Values = "=charts!$A$1:$A$3"

or

With ActiveSheet.ChartObjects
.Item(.Count).Chart.SeriesCollection(1).Values = "=charts!$L$2:$N$2"
End With

Well in my code, the code
Series_1 = "='#of wells'!$C$" + RowLoc + ":$BQ$" + RowLoc
ActiveChart.SeriesCollection(1).Values = Series_1
is equivalent (same as)
ActiveChart.SeriesCollection(1).Values = "='#of wells'!$C$45:$BQ45"
which appears to be what you show.
Error message: "Unable to set the Values property of te Series clause".

 

[Tushar Mehta]

The one time that that can happen is if all the cells in the specified range
are empty.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Robert Baer]

The one time that that can happen is if all the cells in the specified range
are empty.

Most definitely not empty; B43..BQ79 are completely populated.
Courtesy of your suggestions, i now can make multiple charts spaced
the way i want, and have the titles what i want.
Had to make a minor change, tho.
But still cannot change the series references.
What i have so far:

Sub Macro7()
'
' Macro7: Chart titles and positions change OK; cannot change series

TitleName = "Stripper Well Survey - "
Windows("StripperWellsMod.xls").Activate
Sheets("Charts").Select
Range("A1").Select
SheetRow = 1
ChartNum = 1
CTitle = TitleName + Sheets("#of wells").Cells(43 + ChartNum, 2).Value
' With Worksheets("Charts").ChartObjects(1).Chart <---does not work here
' so be eXplicit..
With Worksheets("Charts").ChartObjects("Chart 1").Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With
For ChartNum = 2 To 5
CTitle = TitleName + Sheets("#of wells").Cells(43 + ChartNum, 2).Value
RowLoc = LTrim(Str$(43 + ChartNum))
Series_1 = "='#of wells'!$C$" + RowLoc + ":$BQ$" + RowLoc
Series_2 = "=Production!$C$" + RowLoc + ":$BQ$" + RowLoc
With Worksheets("Charts")
.Paste
With .ChartObjects(.ChartObjects.Count)
.Top = Worksheets("Charts").Cells(SheetRow + 21, 1).Top
.Left = Worksheets("Charts").Cells(SheetRow + 21, 1).Left
' Following does not work; "Unable to set the Values property of the
Series class"
' .Chart.SeriesCollection(1).Values = Series_1
With .Chart
.HasTitle = True
.ChartTitle.Text = CTitle
.ChartArea.Copy
End With
End With
End With
With ActiveSheet.ChartObjects
.Item(.Count).Chart.SeriesCollection(1).Values = Series_1
End With
SheetRow = SheetRow + 21
Next ChartNum
' Following does not work; "Unable to set the Values property of the
Series class"
' With ActiveSheet.ChartObjects
' .Item(.Count).Chart.SeriesCollection(1).Values = "=charts!$L$2:$N$2"
' End With

' deliberate error to force allowance of debug
ActiveChart.ChartTitle = "foo"
End Sub