adding a data series to a chart with variable inputs.

[Brian S]

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.

 

[paul.robinson]

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

 

[Brian S]

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 Where:=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