having an issue where the macro below (and other variations I've tried) is for some reason refusing to add the 2nd data series (Cumulative Hours) to a chart in a project staffing sheet I'm working on. Is anyone able to help me troubleshoot?
Sub staffchartreset()
Dim fterow As Long
Dim choursrow As Long
Dim stafflastcol As Long
Dim lastcolletter As String
Dim s1 As Series
Dim s2 As Series
Sheets("Staffing Plan").Select
stafflastcol = Cells(14, Columns.Count).End(xlToLeft).Column
fterow = ThisWorkbook.Sheets("Staffing Plan").Range("F:F").Find(What:="FTE", LookIn:=xlValues).Row
choursrow = fterow + 1
lastcolletter = Col_Letter(stafflastcol)
Sheets("Staffing Chart").Select
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Delete
ActiveChart.FullSeriesCollection(1).Delete
Set s1 = ActiveChart.SeriesCollection.NewSeries
Set s2 = ActiveChart.SeriesCollection.NewSeries
With s1
.Name = "FTE"
.AxisGroup = xlPrimary
.Values = "='Staffing Plan'!$K$" & fterow & ":$" & lastcolletter & "$" & fterow
.XValues = "='Staffing Plan'!$K$14:$" & lastcolletter & "$14"
End With
With s2
.Name = "Cumulative Hours"
.AxisGroup = xlSecondary
.Values = "='Staffing Plan'!$K$" & choursrow & ":$" & lastcolletter & "$" & choursrow
.XValues = "='Staffing Plan'!$K$14:$" & lastcolletter & "$14"
End With
End Sub
having an issue where the macro below (and other variations I've tried) is for some reason refusing to add the 2nd data series (Cumulative Hours) to a chart in a project staffing sheet I'm working on. Is anyone able to help me troubleshoot?
Sub staffchartreset()
Dim fterow As Long
Dim choursrow As Long
Dim stafflastcol As Long
Dim lastcolletter As String
Dim s1 As Series
Dim s2 As Series
Sheets("Staffing Plan").Select
stafflastcol = Cells(14, Columns.Count).End(xlToLeft).Column
fterow = ThisWorkbook.Sheets("Staffing Plan").Range("F:F").Find(What:="FTE", LookIn:=xlValues).Row
choursrow = fterow + 1
lastcolletter = Col_Letter(stafflastcol)
Sheets("Staffing Chart").Select
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Delete
ActiveChart.FullSeriesCollection(1).Delete
Set s1 = ActiveChart.SeriesCollection.NewSeries
Set s2 = ActiveChart.SeriesCollection.NewSeries
With s1
.Name = "FTE"
.AxisGroup = xlPrimary
.Values = "='Staffing Plan'!$K$" & fterow & ":$" & lastcolletter & "$" & fterow
.XValues = "='Staffing Plan'!$K$14:$" & lastcolletter & "$14"
End With
With s2
.Name = "Cumulative Hours"
.AxisGroup = xlSecondary
.Values = "='Staffing Plan'!$K$" & choursrow & ":$" & lastcolletter & "$" & choursrow
.XValues = "='Staffing Plan'!$K$14:$" & lastcolletter & "$14"
End With
End Sub
Share
Improve this question
asked 2 days ago
cadialgcadialg
112 bronze badges
3
|
1 Answer
Reset to default 0You can assign data to a series using a Range - no need to convert that to a string. Try something like this:
Sub staffchartreset()
Dim fterow As Long, choursrow As Long
Dim stafflastcol As Long, ws As Worksheet, wsCht As Chart
Dim rngX As Range
Set ws = ThisWorkbook.Worksheets("Staffing Plan") 'assuming sheets are in same workbook as this code
Set wsCht = ThisWorkbook.Sheets("Staffing Chart")
stafflastcol = ws.Cells(14, ws.Columns.Count).End(xlToLeft).Column
fterow = ws.Range("F:F").Find(What:="FTE", lookat:=xlWhole, LookIn:=xlValues).row
choursrow = fterow + 1
'remove any existing series
Do While wsCht.FullSeriesCollection.Count > 0
wsCht.FullSeriesCollection(1).Delete
Loop
Set rngX = ws.Range(ws.Cells(14, "K"), ws.Cells(14, stafflastcol))
With wsCht.SeriesCollection.NewSeries
.Name = "FTE"
.AxisGroup = xlPrimary
'you can use the range directly - no need for that string concatenation...
.Values = ws.Range(ws.Cells(fterow, "K"), ws.Cells(fterow, stafflastcol))
.XValues = rngX
Debug.Print .Formula 'for checking...
End With
With wsCht.SeriesCollection.NewSeries
.Name = "Cumulative Hours"
.AxisGroup = xlSecondary
.Values = ws.Range(ws.Cells(choursrow, "K"), ws.Cells(choursrow, stafflastcol))
.XValues = rngX
Debug.Print .Formula
End With
End Sub
Debug.Print "='Staffing Plan'!$K$" & choursrow & ":$" & lastcolletter & "$" & choursrow
and same for XValues, to see if those are the ranges you expect? – Tim Williams Commented 2 days ago