IF Statement Excel VBA

Question

strTemp = ActiveSheet.PivotTables("BreakCon").PivotFields("Service Type").PivotItems("International Data")
        If Err = 0 Then FieldExists = True Else FieldExists = False
        If FieldExists = True Then
          With ActiveSheet.PivotTables("BreakCon").PivotFields("Service Type")
            .PivotItems("International Data").Visible = False
            .PivotItems("Non Contracted").Visible = False
        ElseIf FieldExists = False Then
         With ActiveSheet.PivotTables("BreakCon").PivotFields("Service Type")
            .PivotItems("Non Contracted").Visible = False

        Else
            MsgBox ("Error")
        End If

Occasionally the data passing through the code has "International Data" however occasionally it doesn't so i added this If statement to try and check whether "International Data" is in the Pivot Table and if not then go to the next if statement. Can anyone see an issue with this code? i have tried all of the ways i know but have hit a brickwall! Cheers!

Which line causes an error?

Jordan
– Jordan

2016-06-24 14:02:03 +00:00
Commented Jun 24, 2016 at 14:02 — Jordan
– Jordan, Commented Jun 24, 2016 at 14:02

Scott Holtzman · Accepted Answer · 2016-06-24 14:02:20Z

2

The only way to do this (AFAIK) is to loop through the pivot items in the pivot field and check for the existence of the desired item.

I also assume that in your above code, you may be using On Error Resume Next to brute force you're way through the issue, but the method below as the advantage of using built VBA functionality to smoothly handle the issue.

See below:

Dim pt as PivotTable
Dim pf as PivotField
Dim pi as PivotItem

Set pt = Worksheet("mySheet").PivotTables("BreakCon")
Set pf = pt.PivotFields("Service Type")

For each pi in pf.PivotItems

    If pi.Name = "International Data" Then 
        pf.PivotItems("International Data").Visible = False
        Exit For
    End If

Next

pf.PivotItems("Non Contracted").Visible = False

answered Jun 24, 2016 at 14:02

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crane92 Over a year ago

Cheers for that Scott, yeah i had used a temporary way to get around the issue whilst i ran it the first time but that code you gave works great!

Collectives™ on Stack Overflow

IF Statement Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related