VBA Help Formatting Chart if No PIvot Value Provided - Error 1004 Unable to Set AxisGroup Property of the Series Class

In the following code :
I ran into a problem when trying to run the FormatChartSeries macro in the printcharts macro.
Error 1004 occurs on the following line:  .SeriesCollection(lngIndex).AxisGroup = 2
It occurs because CASE "Active, Approved" and "Planned" or blank and not contained in the pivot table.
Is there a way to tell it to just format on AxisGroup = 1

This is my last hurdle for the day. I appreciate all your help.
Sub FormatChartSeries5()
'
' Format Utilization Chart Series Macro
' Format the pivot chart into area charts for Utilization, Min and Max. Format is xlLineMarkers for GNE FTE and GNE FTE + Contractor. Macro recorded 1/25/2008 by GenenUser
'
    Dim lngIndex As Long
    Sheets("Utilization Chart").Select
    With ActiveChart
        For lngIndex = 1 To .SeriesCollection.Count
            Select Case .SeriesCollection(lngIndex).Name
                Case "Active", "Approved", "Planned"
                    .SeriesCollection(lngIndex).ChartType = xlColumnStacked
                    .SeriesCollection(lngIndex).AxisGroup = 1
                    .HasTitle = True
                    .ChartTitle.Text = "='Utilization Pivot'!R8C2"
                Case "Utilization", "Min", "Max"
                    .SeriesCollection(lngIndex).ChartType = xlLineMarkers
                    .SeriesCollection(lngIndex).AxisGroup = 2
                    .HasTitle = True
                    .ChartTitle.Text = "='Utilization Pivot'!R8C2"
                Case Else
                    .SeriesCollection(lngIndex).ChartType = xlColumnStacked
                    .SeriesCollection(lngIndex).AxisGroup = 1
            End Select
        
        Next lngIndex
    
    End With
    
End Sub
 
 
Sub PrintPivotCharts5()
 'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable
 
  For Each pf In pt.PageFields
  
    On Error Resume Next
    For Each pi In pf.PivotItems
        pi.Delete
    Next pi
    On Error GoTo 0
    
    pt.RefreshTable
    
    For Each pi In pf.PivotItems
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
        Call FormatChartSeries5
'      ActiveSheet.PrintOut
      ActiveSheet.PrintPreview
      'print preview for testing
    Next
  Next pf
End Sub

Open in new window

kreepaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kreepaAuthor Commented:
The FormatChartSeries macro work fine and runs all the way through the procedure, But it produces percentvalues on axis 1 and axis 2 and sometimes applies the Max on axisgroup1 and the Min on axisgroup2. THIS MAKES THE CHART LOOK FUNNY AND NOT MAKE SENSE.
0
jeveristCommented:
Hi kreepa,

It looks like we need to check all of the series to make sure that there is at least one that is not "Utilization", "Min" or "Max" before we set any of the these to the secondary axis.  Try the resived routine below.

Jim
Sub FormatChartSeries5()
'
' Format Utilization Chart Series Macro
' Format the pivot chart into area charts for Utilization, Min and Max. Format is xlLineMarkers for GNE FTE and GNE FTE + Contractor. Macro recorded 1/25/2008 by GenenUser
'
    Dim lngIndex As Long, iLineAxisGroup As Long
    Sheets("Utilization Chart").Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "='Utilization Pivot'!R8C2"
        
        iLineAxisGroup = xlPrimary
        For lngIndex = 1 To .SeriesCollection.Count
            Select Case .SeriesCollection(lngIndex).Name
                Case "Utilization", "Min", "Max"
                    iLineAxisGroup = xlPrimary
                Case Else
                    iLineAxisGroup = xlSecondary
                    Exit For
            End Select
        Next lngIndex
        
        For lngIndex = 1 To .SeriesCollection.Count
            Select Case .SeriesCollection(lngIndex).Name
                Case "Utilization", "Min", "Max"
                    .SeriesCollection(lngIndex).ChartType = xlLineMarkers
                    .SeriesCollection(lngIndex).AxisGroup = iLineAxisGroup
                Case Else ' Including "Active", "Approved", "Planned" and anything else
                    .SeriesCollection(lngIndex).ChartType = xlColumnStacked
                    .SeriesCollection(lngIndex).AxisGroup = xlPrimary
            End Select
        Next lngIndex
    End With
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.