Set PivotField to predefined value with check

[s80NL]

I'm having a Pivot table in a sheet and I want to set a PivotField at
predefined value when that value exists in the fields list.

The following code works fine but gives an error (-Run-time erro
'1004': Unable to get the PivotTables property of the Worksheet class-
when the predefined value does not exists in the fields list...

How can I transform this into a if-else construction where I check th
presence of the predefined value first? (If predefined value isn'
available then the option '(blank)' should be selected.)


Code

 

[Guest]

You can loop through all the items in that pivot table field, like this:

Function CheckFld(TestStr As String) As Boolean
Dim x As Long
For x = 1 To
ActiveSheet.PivotTables("PivotCC").PivotFields("Customerspecs").PivotItems.Count
If
ActiveSheet.PivotTables("PivotCC").PivotFields("Customerspecs").PivotItems(x)
= TestStr$ Then
CheckFld = True
Exit Function
End If
Next x
CheckFld = False
End Function

You can call this function to find out if a particular value exists in the
Cusomerspecs field:

If CheckFld("CC missing") Then
ActiveSheet.PivotTables("PivotCC").PivotFields("Customerspecs"). _
CurrentPage = "CC missing"
End If

Hope this helps,

Hutch

 

[s80NL]

Tom,

If made the following out of your suggestion:


PHP code:
--------------------
Function CheckFld(WS As String, PT As String, PF As String, OptionX As String) As Boolean
Dim i As Long

Sheets(WS).Select
For i = 1 To ActiveSheet.PivotTables(PT).PivotFields(PF).PivotItems.Count
If ActiveSheet.PivotTables(PT).PivotFields(PF).PivotItems(i) = OptionX$ Then
CheckFld = True
Exit Function
End If
Next i
CheckFld = False
End Function
Sub RefreshSettings()
For i = 2 To 8
Dim WS As String, PT As String, PF As String, Option1 As String, Option2 As String, Option3 As String
WS = s0.Range("A" & i).Value
PT = s0.Range("B" & i).Value
PF = s0.Range("C" & i).Value
Option1 = s0.Range("D" & i).Value
Option2 = s0.Range("E" & i).Value
Option3 = s0.Range("F" & i).Value

Sheets(WS).Select
If CheckFld(WS, PT, PF, Option1) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).CurrentPage = Option1
ElseIf CheckFld(WS, PT, PF, Option2) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).CurrentPage = Option2
ElseIf CheckFld(WS, PT, PF, Option3) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).CurrentPage = Option3
End If
Next i
End Sub
--------------------


As you can see I've put all information regarding WorkSheet names,
PivotTable names, PivotField names and 3 options in a sheet named s0.

But now I get an error for: -For i = 1 To
ActiveSheet.PivotTables(PT).PivotFields(PF).PivotItems.Count-

Run-time error '1004': Unable to get the PivotFields property of the
PivotTables class

Any idea what is going wrong and why? Thanks for your help!

 

[Guest]

I suspect the problem is in your data. Could you have misspelled a field name
(trailing spaces, etc.) or mismatched a field with a pivot table?

I tested your code with a pivot table and it worked fine.

Regards,

Hutch