none
unable to get the pivotfields property of the pivot table class RRS feed

  • Question

  • Hi,

    user specific like what are data's need from sheet1 then need to fill in sheet2 column A then our VBA give as pivot table.

    i used excel array but giving as "unable to get the pivotfields property of the pivot table class" on line Set pf = pt.PivotFields(MyArray(iCount, 1))

    Sub tstold()
    Dim pt As PivotTable

    'The array is declared as a Variant, so it can contain
    'different data types like e.g. numbers and tekst.
    Dim MyArray(1 To 8, 1 To 5) As Variant
    Dim rRange As Range
    Dim iCount As Integer
    Dim iCount2 As Integer

    'We set our range = cell A1:A10
    Set rRange = Sheets("Sheet2").Range("A2:A8")

    'Values are read from cell A1:A10 and to the right.
    For iCount = 1 To 8
       With rRange.Item(iCount)
          MyArray(iCount, 1) = .Value
          MyArray(iCount, 2) = .Offset(0, 1).Value
          MyArray(iCount, 3) = .Offset(0, 2).Value
          MyArray(iCount, 4) = .Offset(0, 3).Value
          MyArray(iCount, 5) = .Offset(0, 4).Value
       End With
    Next

    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)
    'We now write the values back to the spreadsheet, but in reversed order.
    For iCount = 8 To 1 Step -1
       iCount2 = iCount2 + 1
       With rRange.Item(iCount2)
    Set pf = pt.PivotFields(MyArray(iCount, 1))
        pf.Orientation = MyArray(iCount, 2)
        pf.Position = MyArray(iCount, 3)
            
        If pf.Orientation = xlDataField Then
            pf.Function = MyArray(iCount, 4)
            pf.NumberFormat = MyArray(iCount, 5)
        End If
       End With
       
      Next
    End Sub

    Saturday, April 1, 2017 3:46 PM

All replies

  • First, you're only assigning 7 rows of data to rRange, so only 7 rows of data is being transfered to MyArray.  So the last row of data in MyArray remains empty.  This likely is causing the error.  Also, you'll need to replace...

    With rRange.Item(iCount)

    with

    With rRange.Cells(iCount, 1)

    And, lastly, since it doesn't look like you're writing the values back to the worksheet, you don't need (the second instance of With/End With)...

    iCount2 = iCount2 + 1 With rRange.Item(iCount2)

    and

    End With

    And so, it doesn't look that it's necessary to step backwards in your iteration.  Maybe something like this might suffice (untested)...

    Sub tstold()
    
        Dim v As Variant
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim i As Integer
        
        v = Sheets("Sheet2").Range("A2:E8").Value
        
        Set pt = ActiveSheet.PivotTables(1)
        
        For i = LBound(v, 1) To UBound(v, 1)
            Set pf = pt.PivotFields(v(i, 1))
            pf.Orientation = v(i, 2)
            pf.Position = v(i, 3)
            If pf.Orientation = xlDataField Then
                pf.Function = v(i, 4)
                pf.NumberFormat = v(i, 5)
            End If
        Next i
          
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"





    Sunday, April 2, 2017 3:13 AM
  • Hi,

    Thank you so much for your kind support. But am getting error in below mentioned line as "Type mismatch".


     pf.Orientation = v(i, 2)


    Thanks,





    Sunday, April 2, 2017 8:59 AM
  • Try running the code again.  This time, when you get the error, click on Debug.  It should highlight the line generating the error.  Then, place your cursor over the "v" part of the array variable.  What is displayed when you do so?  It should be a value between 0 and 4, inclusive.  Is this the case?

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Sunday, April 2, 2017 1:02 PM
  • Hi,

    This is my first array (Date,xlRowField,1,Empty,Empty) in range of A2:E2. if move on cursor on V,  the string is showing with double quotes but numbers are showing without double quotes. So I have found issue with double quotes like  "xlRowField" for  v(i,2). if without double quotes am getting means may my issues will resolve it.

    Thank you..


    Monday, April 3, 2017 3:31 PM