Jtdylktuy

I have built a code into excel to take the data from a pivot table and insert it into a chart, a pivot chart directly linked to the table wont give me the manueverability I am looking for. The reason I have gone through the trouble of making such an 'intricate' code is that for each Plant and Test Info combination, I need it to be a separate entry into the chart.

So the main point of this code is to go through each plant and Test info combination (the nested for commands) then insert the data into the chart. My user will not be changing the column location for x and y, so the offsets work fine.

My problem is that if the plant / test info combination does not exist it enters it onto the chart anyways. When I try to use the goto command and send it to the Next PI2 using an errorhandle, it doesnt work (maybe because of the nested if commands). I was looking around trying to find a command that could send my code to a particular line in the code (i.e just after the graph commands) but didnt have any luck...

Does anyone know of a way to skip to a particular line in the event of an error?

I have added the set of commands to resume at the next PI2, where I say on error goto errhandler, then from errhandler go to the next iteration, but when I run the code and get an error it isnt going through this route, it is instead stopping at the 'intersect' line.

Sub CreatePivotChart()



 Dim PF1 As PivotField

 Dim PI1 As PivotItem

 Dim PI2 As PivotItem

 Dim PF2 As PivotField

 Dim chartcount As Integer

 Dim pt As PivotTable

 Set pt = Worksheets("Pivot Table").PivotTables("PivotTable")



'set up pivot field locations 1 - plant and unit , 2 - test conditions



 Set PF1 = Worksheets("PivotTable").PivotTables("PivotTable").PivotFields("Plant")



 Set PF2 = Worksheets("Pivot Table").PivotTables("PivotTable").PivotFields("Test Info")



 'clear the chart from previous run

  chartcount = 0

  Sheets("Pivot Table Graph").ChartObjects("Chart 1").Chart.ChartArea.ClearContents



  On Error GoTo ErrHandler



 'find each visible unit

  For Each PI1 In PF1.PivotItems



  If PI1.Visible = True Then

     Unit = PI1.Name



     For Each PI2 In PF2.PivotItems



     'for each unit and test condition find the information at their intersection

       If PI2.Visible = True Then

       TC = PI2.Name





    'find the information that corresponds to each unit/test condition combination

    Intersect(pt.PivotFields("Plant").PivotItems(Unit).DataRange.EntireRow, pt.PivotFields("Test Info").PivotItems(TC).DataRange).Select

    Selection.Offset(-1, 0).Select

    ForXRanges = "='Pivot Table'!" & Selection.Address

    Selection.Offset(0, 1).Select

    ForYRanges = "='Pivot Table'!" & Selection.Address

    ForRangesName = Unit & "_" & TC



    'for each combination create a new series on the chart

    chartcount = chartcount + 1

    Sheets("Pivot Table Graph").ChartObjects("Chart 1").Activate

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(chartcount).Name = ForRangesName

    ActiveChart.SeriesCollection(chartcount).XValues = ForXRanges

    ActiveChart.SeriesCollection(chartcount).Values = ForYRanges



End If



NextIteration:

Next PI2



End If

Next PI1



Exit Sub



ErrHandler:

Resume NextIteration:



End Sub

You can handle errors without goto in VBA like this:

Sub ErrorHandling()

Dim A, d



On Error Resume Next    

REM Line that throws an error

A = A / 0

REM Store details about your error before it gets cleared

d = Err.Description



On Error GoTo 0



REM You see and can handle your error message here

MsgBox d  

End Sub

On Error Resume Next Disables throwing errors

On Error GoTo 0 Enables throwing errors and clears the Err object

I ended up answering my own questions, by continuing to look through old posts etc., I found http://www.cpearson.com/excel/errorhandling.htm to be extremely helpful.

It turned out I was trying to use two goto commands, first to goto the error handler then goto next iteration. What I needed to do was change the second goto, to resume.

Thanks for all the help guys, the above code runs perfectly!

A better approach would be to test your data with an if statement to ensure your data is valid. If not, do not proceed with the code block that may generate an error.

In your example, this may work... change this:

'find the information that corresponds to each unit/test condition combination

Intersect(pt.PivotFields("Plant").PivotItems(Unit).DataRange.EntireRow, pt.PivotFields("Test Info").PivotItems(TC).DataRange).Select

Selection.Offset(-1, 0).Select

ForXRanges = "='Pivot Table'!" & Selection.Address

Selection.Offset(0, 1).Select

ForYRanges = "='Pivot Table'!" & Selection.Address

ForRangesName = Unit & "_" & TC



'for each combination create a new series on the chart

chartcount = chartcount + 1

Sheets("Pivot Table Graph").ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(chartcount).Name = ForRangesName

ActiveChart.SeriesCollection(chartcount).XValues = ForXRanges

ActiveChart.SeriesCollection(chartcount).Values = ForYRanges

To this:

'find the information that corresponds to each unit/test condition combination

Set isect = Application.Intersect(pt.PivotFields("Plant").PivotItems(Unit).DataRange.EntireRow, pt.PivotFields("Test Info").PivotItems(TC).DataRange)



If isect Is Nothing Then 

    'Msgbox "Ranges do not intersect"

Else

    isect.Select 



    Selection.Offset(-1, 0).Select

    ForXRanges = "='Pivot Table'!" & Selection.Address

    Selection.Offset(0, 1).Select

    ForYRanges = "='Pivot Table'!" & Selection.Address

    ForRangesName = Unit & "_" & TC



    'for each combination create a new series on the chart

    chartcount = chartcount + 1

    Sheets("Pivot Table Graph").ChartObjects("Chart 1").Activate

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(chartcount).Name = ForRangesName

    ActiveChart.SeriesCollection(chartcount).XValues = ForXRanges

    ActiveChart.SeriesCollection(chartcount).Values = ForYRanges

End If

I'm not able to test this since I don't have your workbook, but if it doesn't work it should demonstrate the approach.