Problems selecting PivotItems in vba

Question

I have the following code that loops trough a range and formats some PivotItems, according to the value of the range. The code jumps from one sheet (with the students name and answers) to the other (with the pivot table with two RowLabels, QuestionNo and StudentName), and when it finds the correct PivotItem, change its format.

Sub Macro1()

    Dim student As String, q1 As String
    Dim pt as PivotTable

    Worksheets("SheetPivot").Activate
    Set pt = ActiveSheet.PivotTables(1)

    'Jumps to the Sheet with the range to loop
    Worksheets("QuizState").Activate
    'Goes to the first value
    Range("A1").Select

    'Sarts the loop
    Do Until IsEmpty(ActiveCell.Value)
        'Determines what type of value is.
        student = ActiveCell.Value 
        q1 = ActiveCell.Offset(0,1).Value
        If q1 = "WRONG" then
             'Jumps to Sheet with PivotTable
              Worksheets("SheetPivot").Activate
              pt.PivotFields("StudentName").PivotItems(student).LabelRange.Select
              Selection.Interior = 65535  'Yellow
              Worksheets("QuizState").Activate
        End If
        ActiveCell.Offset(1,0).Select
    Loop
 End Sub

Now, for some reason it works sometimes, sometimes it returns a Run-time error '1004': Unable to get the PivotItems property of the PivotField Class which I guess is due to not finding the student item, but it sure is there.

Can someone help me to debbug the code?

Siddharth Rout · Accepted Answer · 2012-03-07T07:04:40.977

Can someone help me to debbug the code?

I can help you debug it if you can upload a sample of the workbook in wikisend.com and share the link here?

which I guess is due to not finding the student item, but it sure is there.

Try this

student = Trim(ActiveCell.Value)

Also you can rewrite your code without using .Select and .Activate. Your code will run much faster :) For example (THE BELOW CODE IS UNTESTED)

Sub Macro1()
    Dim student As String, q1 As String
    Dim pt As PivotTable
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws2LastRow As Long, i As Long

    Set ws1 = Worksheets("SheetPivot")
    Set pt = ws1.PivotTables(1)

    Set ws2 = Worksheets("QuizState")

    With ws2
        ws2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 1 To ws2LastRow
            'Determines what type of value is.
            student = Trim(.Range("A" & i).Value)
            q1 = UCase(Trim(.Range("B" & i).Value))
            If q1 = "WRONG" Then
                'Jumps to Sheet with PivotTable
                pt.PivotFields("StudentName").PivotItems(student).LabelRange.Interior = 65535  'Yellow
            End If
        Next
    End With
 End Sub

This indeed does what I need. For some reason, the code I wrote does find the Pivot Item, but does'n select it in all cases (the name was already trimmed). Bypassing the selection, the code works like a charm. Thanks a lot. — Pragabhava, Mar 09 '12 at 02:07

Problems selecting PivotItems in vba

1 Answers1