VBA Code for Pivot Table Sum Field Invalid Qualifier

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for anyone's suggestions. I will give feedback on any suggested solutions.

Why am I getting a "Compile error: Invalid Qualifier" on "xlDataField" within the line:

Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With

When I comment out that part of the code it runs fine. The following is the entire code.

Code:
Sub CreatePivotTable()


'Dimensioning Variables
    Dim DataSourceSheet As Worksheet
    Dim DataPivotCache As PivotCache
    Dim DataPivotTable As PivotTable
    Dim StartPivot As String
    Dim PivotSourceData As String
    Dim ColumnLetter As String
    Dim LastRowDF As Long
    Dim LastColumnDF As Long
    Dim Wks As Worksheet




'Turn off Screen Mirroring/Updating
    Application.ScreenUpdating = False
    
'Ensure all Worksheets within this Workbook are calculated
    Application.Calculation = xlManual
    
'Checks to see if the new sheet exists "Pivot.Table.Data" and deletes it if so
    For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Pivot.Table.Data" Then
          Sheet.Delete
     End If
    Next Sheet
    
'Loop Workbook to calculate all spreadsheets
    For Each Wks In ActiveWorkbook.Worksheets
        Wks.Calculate
    Next
        
        Set Wks = Nothing


'Activate "Date.Formatted" worksheet
    Worksheets("Data.Formatted").Activate
        
'Find last row
    LastRowDF = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row


        
'Finding last column
    LastColumnDF = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        
'Convert To Column Letter
  ColumnLetter = Split(Cells(1, LastColumnDF).Address, "$")(1)


'Determine the data range to pivot
    PivotSourceData = Sheets("Data.Formatted").Name & "!" & Range("B2:" & ColumnLetter & LastRowDF).Address(ReferenceStyle:=xlR1C1)


'Creating the new destination sheet "Pivot.Table.Data" as the
    ActiveWorkbook.Sheets.Add(after:=Worksheets("Well.Attributes")).Name = "Pivot.Table.Data"
    Set DataSourceSheet = Sheets("Pivot.Table.Data")


'Where do you want Pivot Table to start?
    StartPivot = DataSourceSheet.Name & "!" & DataSourceSheet.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
    Set DataPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotSourceData)


'Create Pivot table from Pivot Cache
    Set DataPivotTable = DataPivotCache.CreatePivotTable(TableDestination:=StartPivot, TableName:="PivotTable1")


'Calculate "Pivot.Table.Data"
    Sheets("Pivot.Table.Data").Calculate


'Screen Updating On
    Application.ScreenUpdating = True


'Add item to the Report Filter (Reserve Category)
    DataPivotTable.PivotFields("RC").Orientation = xlPageField
  
'Add item to the Column Labels (Date)
    DataPivotTable.PivotFields("Date").Orientation = xlColumnField
    
'Add item to the Row Labels (PHDWin Case Name)
    DataPivotTable.PivotFields("Well Name").Orientation = xlRowField
    
'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With
    
'Format Pivot Field
    'DataPivotTable.PivotFields("Year").NumberFormat = "#,##0"
    
'Turn on Automatic updates/calculations - like screenupdating to speed up code
    DataPivotTable.ManualUpdate = False






End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Why am I getting a "Compile error: Invalid Qualifier" on "xlDataField" within the line:

Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
        With DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField.Function = xlSum
            End With


That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block.

Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With


The With block provides a more efficient form than this equivalent code...
Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum
 
Last edited:
Upvote 0
Hi Jerry and thanks for the post.

I used the following and it gave me the error the second line "Run-time error '1004' Unable to set the Function property of the PivotField class." When I went to the Pivot Table, everything seem to be fine. I couldn't understand what may have gone wrong.

Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
    DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
[B]    DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum[/B]

So you posted the following where for the second code you said
"The With block provides a more efficient form than this equivalent code...",

but on the first code you said:

"That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block."

so I was a bit confused. I took that you use the "With Block" is incorrect, but is more efficient. Well I tried the second code because the first one looked like what I submitted that it would not work.

That syntax for a With block is incorrect. The With keyword is used to reference an Object from a statement or multiple statements within the With block.

Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With

The With block provides a more efficient form than this equivalent code...
Code:
'Add item to the Data Field to either Count or Sum. In this Case Sum
DataPivotTable.PivotFields("Gas.Volume.Gross").Orientation = xlDataField
DataPivotTable.PivotFields("Gas.Volume.Gross").Function = xlSum
 
Upvote 0
Sorry if my explanation was unclear.

Your procedure should work if you use this code in lieu of your original code:

Code:
With DataPivotTable.PivotFields("Gas.Volume.Gross")
   .Orientation = xlDataField
   .Function = xlSum
End With

That worked for me, does it work for you?

The second code example I provided was intended to show you that when one uses a With block, it is equivalent to using a series of statements that repetitively list the same object (in this case, your PivotTable).
It turns out that in this case, those two code examples are not equivalent and the second example fails. I haven't seen that happen before and I suspect this is a rare exception that occurs when adding pivot table fields.

One other alternative is to use the AddDataField method.
Code:
 DataPivotTable.AddDataField DataPivotTable.PivotFields("Gas.Volume.Gross"), _
   "Sum of Gas.Volume.Gross", xlSum
 
Upvote 0
Thanks so much Jerry. I tested the codes and they both work.

I think the first code I was typing it in incorrectly although I tried like ten times.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top