| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Cannot change Series references
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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Cannot change Series references
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Cannot change Series references
Tushar Mehta wrote:
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". |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Cannot change Series references
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" wrote: Tushar Mehta wrote: 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". |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Cannot change Series references
Tushar Mehta wrote:
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fill Series Dates: not letting me change the series from year to m | Excel Discussion (Misc queries) | |||
| Excel 2007 chart series - unable to use structured references | Charts and Charting in Excel | |||
| How do I change a number in one cell to change a series of cells? | Excel Discussion (Misc queries) | |||
| Formulas in chart series references | Charts and Charting in Excel | |||
| how do I change a line series to a column series in excel? | Charts and Charting in Excel | |||
Linear Mode
