excel - Macro not adding a second series to a chart? - Stack Overflow

admin管理员组

文章数量:1346338

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

• excel
• vba
• charts

Share Improve this question Follow asked 2 days ago cadialgcadialg 1122 bronze badges 3

• What exactly happens when it "refuses"? – Tim Williams Commented 2 days ago
• Nothing, no error, it just doesn't add the 2nd series. – cadialg Commented 2 days ago
• Have you tried (eg) adding 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You 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

本文标签： excelMacro not adding a second series to a chartStack Overflow