Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding a data series to a chart with variable inputs.
I recorded a macro to make a chart, and wanted to replace some of the series
with variables. Here is the part that I get the error on: "advanced homeland securityact" is the name of the tab the data is on and chrt is a variable for the row. ActiveChart.SeriesCollection(2).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(2).Values = _ "='Advanced Homeland Securityact'!" & Range(Cells(chrt, 9), Cells(chrt, 20)) ActiveChart.SeriesCollection(2).Name = "=""actuals$""" THanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding a data series to a chart with variable inputs.
Hi
Not enough enough for me to spot a question and not enough info to reproduce and fathom your error. If you gave me the whole macro would you have to kill me.... regards Paul On Jan 28, 6:24*pm, Brian S wrote: I recorded a macro to make a chart, and wanted to replace some of the series with variables. Here is the part that I get the error on: "advanced homeland securityact" is the name of the tab the data is on and chrt is a variable for the row. * * ActiveChart.SeriesCollection(2).XValues = _ * * * * "='Advanced Homeland Securityact'!R1C9:R1C20" * * ActiveChart.SeriesCollection(2).Values = _ * * * * "='Advanced Homeland Securityact'!" & Range(Cells(chrt, 9), Cells(chrt, 20)) * * ActiveChart.SeriesCollection(2).Name = "=""actuals$""" THanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding a data series to a chart with variable inputs.
I am sure there are lots of ways to make this better, but here it is. No
need for anyone to die. Sub nbfactuals() For a = 1 To 2 If a = 1 Then datasheet = "budget" If a = 2 Then datasheet = "actuals" programname = "Advanced Homeland Security" programnamelen = Len(programname) If programnamelen = 31 Then programnameleft = Left(programname, 28) Else programnameleft = programname End If programnameleft = Left(programnameleft, programnamelen) datasheetleft = Left(datasheet, 3) sheetname = programnameleft & datasheetleft Worksheets.Add.Name = sheetname c = Worksheets.Count Worksheets(sheetname).Move After:=Sheets(c) Worksheets(datasheet).Activate Cells(1, 1).EntireRow.Delete Set myRange = ActiveCell.CurrentRegion databottomrow = myRange.Rows.Count Cells(databottomrow, 1).EntireRow.Delete Cells(1, 1).Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:=programname Cells(1, 1).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Worksheets(sheetname).Activate Cells(1, 1).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(9, 10, 11, _ 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 Selection.End(xlToRight).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToLeft)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Cells(100, 1).Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells(1, 1).Activate Range("a1:a99").EntireRow.Delete Set myRange2 = ActiveCell.CurrentRegion projectcount = myRange2.Rows.Count For cum = 2 To projectcount Cells(projectcount + 9 + cum, 9).Formula = Cells(cum, 9).Value For q = 10 To 20 Cells(projectcount + 9 + cum, q).Formula = Cells(projectcount + 9 + cum, (q - 1)).Value + Cells(cum, q).Value Next q Next cum Cells(2, 8).Value = "budget" Next a For chrt = 2 To projectcount + 1 projectname = Cells(chrt, 4).Value If projectname = "Grand Total" Then projectname = programnameleft projectnamelen = Len(projectname) If projectnamelen = 31 Then projectnameleft = Left(projectname, 28) Else projectnameleft = projectname End If projectnameleft = Left(projectnameleft, projectnamelen) Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ "Line - Column on 2 Axes" ActiveChart.SeriesCollection(1).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(1).Values = _ "='Advanced Homeland Securitybud'!" & Range(Cells(chrt, 9), Cells(chrt, 20)) ActiveChart.SeriesCollection(1).Name = _ "='Advanced Homeland Securityact'!r1c8" ActiveChart.SeriesCollection(2).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(2).Values = _ "='Advanced Homeland Securityact'!" & Range(Cells(chrt, 9), Cells(chrt, 20)) ActiveChart.SeriesCollection(2).Name = "=""actuals$""" ActiveChart.SeriesCollection(3).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(3).Values = _ "='Advanced Homeland Securitybud'!" & Range(Cells(chrt + 9, 9), Cells(chrt + 9, 20)) ActiveChart.SeriesCollection(3).Name = "=""cum bud""" ActiveChart.SeriesCollection(4).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(4).Values = _ "='Advanced Homeland Securityact'!" & Range(Cells(chrt + 9, 9), Cells(chrt + 9, 20)) ActiveChart.SeriesCollection(4).Name = "=""cum act""" ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=projectnameleft With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlCategory, xlSecondary) = False .HasAxis(xlValue, xlPrimary) = True .HasAxis(xlValue, xlSecondary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale ActiveChart.Axes(xlCategory, xlSecondary).CategoryType = xlCategoryScale ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlTop ActiveChart.HasDataTable = True ActiveChart.DataTable.ShowLegendKey = True Next chrt End Sub " wrote: Hi Not enough enough for me to spot a question and not enough info to reproduce and fathom your error. If you gave me the whole macro would you have to kill me.... regards Paul On Jan 28, 6:24 pm, Brian S wrote: I recorded a macro to make a chart, and wanted to replace some of the series with variables. Here is the part that I get the error on: "advanced homeland securityact" is the name of the tab the data is on and chrt is a variable for the row. ActiveChart.SeriesCollection(2).XValues = _ "='Advanced Homeland Securityact'!R1C9:R1C20" ActiveChart.SeriesCollection(2).Values = _ "='Advanced Homeland Securityact'!" & Range(Cells(chrt, 9), Cells(chrt, 20)) ActiveChart.SeriesCollection(2).Name = "=""actuals$""" THanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart data scrunches to left when adding X axis date series | Charts and Charting in Excel | |||
Selecting series in a chart based on a range and manual inputs | Excel Discussion (Misc queries) | |||
adding a line to show average for a data series on a chart | Charts and Charting in Excel | |||
Adding data series to chart via macro | Charts and Charting in Excel | |||
Adding a data series to a chart | Excel Programming |