Set PivotField to predefined value with check

  • Thread starter Thread starter s80NL
  • Start date Start date
S

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
 
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
 
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!
 
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
 
Back
Top