Цикл по PivotItems: ошибка времени выполнения 91

У меня есть набор данных на листе, который может каждый раз меняться. Я создаю сводную таблицу из этих данных, но возможно, что одна из сводных элементов не существует. Например:

.PivotItems("Administratie").Visible = False

Если это конкретное значение отсутствует в моем наборе данных, скрипт VBA завершается ошибкой, говоря, что он не может определить элемент в указанном поле. (ошибка 1004)

Поэтому я подумал, что цикл может работать. У меня есть следующее:

Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems

For Each pvtItem In pvtField.pvtItems
        pvtItem.Visible = False
Next

Но это дает мне ошибку 91 в строке For Each pvtItem:

Object variable or With block variable not set

Я думал, что объявил переменные достаточно хорошо, но я, скорее всего, упускаю что-то очевидное ...

15.12.2008 12:02:21
9 ОТВЕТОВ

Попробуйте что-то вроде этого:

Public Function Test()
    On Error GoTo Test_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    ' Change "Pivot" to the name of the worksheet that has the pivot table.
    ' Change "PivotTable1" to the name of the pivot table; right-click on the
    ' pivot table, and select Table Options... from the context menu to get the name.
    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        Debug.Print "Pivot Field: " & pvtField.Name
        For Each pvtItem In pvtField.VisibleItems
            pvtItem.Visible = False
        Next
    Next

Exit Function

Test_EH:
    Debug.Print pvtItem.Name & " error(" & Err.Number & "): " & Err.Description
    Resume Next

End Function

Если вы хотите, чтобы функция просто проверяла существование сводного элемента, вы можете использовать что-то вроде этого:

Public Function PivotItemPresent(sName As String) As Boolean
    On Error GoTo PivotItemPresent_EH

    PivotItemPresent = False

    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        For Each pvtItem In pvtField.VisibleItems
            If pvtItem.Name = sName Then
                PivotItemPresent = True
                Exit Function
            End If
        Next
    Next

    Exit Function

PivotItemPresent_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Function

End Function

Вы можете вызвать это из своего кода следующим образом:

If PivotItemPresent("name_of_the_thing") Then
    ' Do something
End If
0
16.12.2008 15:40:37
For Each pvtField In Worksheets("my_sheet").PivotTables("my_table").PivotFields
    For Each pvtItem In pvtField.PivotItems
        Debug.Print vbTab & pvtItem.Name & ".Visible = " & pvtItem.Visible
        /*.PivotItems(pvtItem).Visible = False*/ 
    Next
Next
.PivotItems("certain_Item").Visible = True

Это не работает до сих пор ... все переменные все еще видны. Ошибка не отображается, она компилируется, но значения все еще там.
В комментируемой мной строке было мое «изобретение», но оно недействительно.

Изменить: Быстрый вопрос: Могу ли я использовать оператор IF, чтобы проверить, действительно ли определенный сводный элемент находится в данных сводной таблицы? Что-то вроде

If PivotItem("name_of_the_thing") = present Then {
    do_something()
}
0
16.12.2008 11:12:05

Вы не можете сказать " .PivotItems(pvtItem).Visible" вне " With" блока. Скажи " pvtField.PivotItems(pvtItem.Name).Visible = False" вместо этого.

Я также отредактировал свой исходный ответ, включив обработку ошибок, когда Excel не может установить свойство Visible. Это происходит потому, что сводной таблице требуется как минимум одно поле строки, одно поле столбца и один элемент данных, поэтому последний из каждого из них нельзя сделать невидимым.

Вы также получите ошибку 1004 при попытке получить доступ к элементу сводки, который уже невидим; Я думаю, что вы можете игнорировать их.

0
16.12.2008 12:18:45
Извините, я не включил весь код, который у меня есть, этот бит, который я разместил здесь, обернут в блок «С». Спасибо за ваш пересмотр, сейчас попробую. :)
Kablam 16.12.2008 12:40:03

Когда я реализую код, опубликованный Патриком, -

Невозможно установить видимое свойство класса PivotItem

- ошибка брошена.

Microsoft признает, что есть ошибка: M $ help
Но просто скрыть строку ... это не вариант, конечно.

0
16.12.2008 14:30:48
Эта ошибка выдается сразу или после установки других элементов? Excel не может установить для свойства Visible значение false для всех элементов, поскольку для сводной таблицы требуется по крайней мере одно поле строки, одно поле столбца и один видимый элемент данных. Почему вы устанавливаете элементы невидимыми? Что это решает?
Patrick Cuff 16.12.2008 15:21:19
Ответ здесь не подходит, опубликовано ниже :)
Kablam 17.12.2008 08:23:46

Ошибка выдается в конце цикла.
Я объединил оба ответа Патрика в следующее:

With ActiveSheet.PivotTables("Table").PivotFields("Field")

    Dim pvtField As Excel.PivotField
    Dim pvtItem As Excel.PivotItem
    Dim pvtItems As Excel.PivotItems

    For Each pvtField In Worksheets("Sheet").PivotTables("Table").PivotFields
        For Each pvtItem In pvtField.PivotItems
            If pvtItem.Name = "ItemTitle" Then
                pvtField.PivotItems("ItemTitle").Visible = True
            Else
                pvtField.PivotItems(pvtItem.Name).Visible = False
            End If
        Next
    Next
End With

Если Item соответствует определенной строке, этот Item устанавливается в True. Else; Предмет установлен False. При ложном состоянии выдается ошибка.
Я знаю, что для Истинного состояния есть ровно одно совпадение. Хотя, когда я "F8" пробираюсь через макрос, условие True никогда не вводится ...

И это объясняет ошибку, все установлено False. (спасибо, Патрик!)

Заставляет меня вопрос ... что именно ЭТО PivotItem?



Идея:
он решает (или должен решить) следующее: набор данных с переменным размером, где для этой конкретной таблицы интерес представляет один столбец. Из этого столбца мне нужно подсчитать значение и поместить его в таблицу. В таблице есть некоторые условия, а также необходима комбинация с другим столбцом, поэтому PivotTable является лучшим решением.
Проблема в том, что в некоторых наборах данных одно конкретное значение не отображается. Значения, которые появляются, отличаются каждый раз.

0
17.12.2008 08:23:05

Сводные элементы - это отдельные значения в поле (столбец, строка, данные). Я думаю о них как о «корзинах», которые содержат все отдельные элементы данных, которые вы хотите объединить.

Вместо того чтобы просматривать все поля сводной таблицы (столбец, строка и данные), вы можете просто пройти через интересующие вас поля. Например, этот код будет отображать только указанные элементы сводной таблицы для указанного поля:

Public Sub ShowInPivot(Field As String, Item As String)
    On Error GoTo ShowInPivot_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    For Each pvtItem In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields(Field).PivotItems
        If pvtItem.Name = Item Then
            pvtItem.Visible = True
        Else
            pvtItem.Visible = False
        End If
    Next

    Exit Sub

ShowInPivot_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Sub

End Sub

Предположим, у меня есть сводная таблица, показывающая количество проблем для каждого выпуска клиента и место их обнаружения в нашем SDLC. «Customer» и «Release» - это поля столбца, а «Phase» - поле строки. Если бы я хотел ограничить сводную таблицу подсчетом проблем для CustomerA, выпуск 1.2 во время QA, я мог бы использовать вышеприведенный подпункт, например так:

ShowInPivot "Customer", "CustomerA"
ShowInPivot "Release", "1.2"
ShowInPivot "Phase", "QA"
0
17.12.2008 14:22:22
По-прежнему не работает, PivotItem.Name возвращает значение, например, «974» вместо «itemname» ...
Kablam 19.12.2008 10:03:45
РЕШЕНИЕ

Я понял! : D

Dim Table As PivotTable
Dim FoundCell As Object
Dim All As Range
Dim PvI As PivotItem

    Set All = Worksheets("Analyse").Range("A7:AZ10000")
    Set Table = Worksheets("Analyse").PivotTables("tablename")
    For Each PvI In Table.PivotFields("fieldname").PivotItems
        Set FoundCell = All.Find(PvI.Name)
        If FoundCell <> "itemname" Then
            PvI.Visible = False
        End If
    Next

Woohoo

Благодаря MrExcel, ответ был там в конце концов, хотя глубоко похоронен.

1
19.12.2008 10:00:15
Хммм ... Я не вижу разницы в итоговой сводной таблице, когда я запускаю свое решение с помощью ShowInTable () и этого решения, по крайней мере, в моей книге.
Patrick Cuff 19.12.2008 12:36:58
С твоим решением я все еще допускал ошибки ... с этим я не делаю. Когда я F8 пробираюсь по вашему коду, pivotItem.Name возвращает значение, а не имя. При таком решении возвращается строка. Возможно ли, что Excel 2003 является виновником?
Kablam 21.12.2008 14:10:25
Я сомневаюсь в этом; Трудно сказать, что происходит без вашей рабочей книги и как определяется ваша сводная таблица и как вы вызываете код. В обоих решениях элемент сводки создается одинаково, поэтому свойство .Name должно быть одинаковым в обоих.
Patrick Cuff 25.12.2008 15:24:19

У меня тоже было одно и то же сообщение об ошибке, когда я пытался установить pivotitem visible true и false ... это работало ранее, но больше не работало ... я сравнивал два значения и явно не менял строку на целое для сравнения .. при этом ошибка исчезла ..

... поэтому, если вы получите это сообщение, проверьте, сравниваются ли какие-либо значения, чтобы сделать элемент видимым или нет, если сравнивать должным образом ... в противном случае pivotitem имеет значение null и не может сделать это видимым или нет.

0
13.03.2009 01:17:34

У меня была ошибка, в которой говорилось «невозможно установить свойство visible класса элементов сводки» в этой строке:

For Each pi In pt.PivotFields("Fecha").PivotItems
    If pi.Name = ffan Then
        pi.Visible = True
    Else
        pi.Visible = False '<------------------------
    End If
Next pi

Затем я прочитал в Интернете, что мне нужно было отсортировать руководство, а затем очистить кеш. я сделал это, но ошибка все еще появилась ..... тогда я прочитал, что это потому, что у меня была дата в этом поле сводки, поэтому я изменил ее, чтобы мой столбец стал общим числом, а затем дату, которую я хотел установить видимой, я изменил ее на общую номер тоже. тогда нет проблем !!!!!!!!!!!!!! .... вот оно .... я надеюсь, что это может быть полезным, потому что я был в отчаянии !!!

Dim an As Variant
an = UserForm8.Label1.Caption 'this label contains the date i want to see its the pivot item i want to see of my pivot fiel that is "Date"
Dim fan
fan = Format(an, "d m yyyy")  
Dim ffan
ffan = Format(fan, "general number")

Sheets("Datos refrigerante").Activate 'this is the sheet that has the data of the pivottable
Dim rango1 As Range
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select

Set rango1 = Selection
ActiveSheet.Cells(1, 1).Select
rango1.Select

Selection.NumberFormat = "General" 'I change the format of the column that has all my dates

'clear the cache
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
  On Error Resume Next
  pc.Refresh
Next pc

'now select the pivot item i want
Dim pi As PivotItem

Set pt = Sheets("TD Refrigerante").PivotTables("PivotTable2")

'Sets Pivot Table to Manual Sort so you can manipulate PivotItems in PivotField
pt.PivotFields("Fecha").AutoSort xlManual, "Fecha"

'Speeds up code dramatically
pt.ManualUpdate = True

For Each pi In pt.PivotFields("Fecha").PivotItems
    If pi.Name = ffan Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

pt.ManualUpdate = False
pt.PivotFields("Fecha").AutoSort xlAscending, "Fecha"
0
26.09.2011 00:08:54