Excel VBA - code to skip lines / goto command

Question

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

Solution

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.

Comments

[gtwebb]

You mentioned error handling but I don't see any in the code. You should be able to use on error goto see here for explanation

[Vojtěch Dohnal]

stackoverflow.com/questions/6028288/…

[Sarah Hartman]

@gtwebb I have added in my interpretation of the goto command, but I my code is still stopping at the 'intersection', not sure why the error isnt sending it to my handler.

[Yorik]

You should test the intersection instead of capturing the error. Otherwise, you should at least test the error number for the one(s) you expect to happen. Intersect returns a range or Nothing ( msdn.microsoft.com/en-us/library/office/ff835030.aspx )

[Sarah Hartman]

thanks for the suggestion. The only thing I was worried about was nesting another if statement into the code. But it would probably be better than 'ignoring' errors.

[spikey_richie]

And then use debug.print to pump Err.Description out to the immediate window if you want to see that the message hasn't changed.