Ich arbeite an einem sehr langen Makro für meinen Job und bin fast fertig, aber dies ist der letzte Teil und egal, was ich tue, ich scheine keine Lösung zu finden. In dieser PivotTable muss ich „Manager“ als Spalten und alles andere (also alle Monate) im Bereich „Werte“ haben, mit „Werten“ als Zeilen. Nach einigem Herumprobieren scheint es, als ob alle Felder als CubeFields statt als PivotFields registriert sind. Wenn ich dies ausführe, wird, sobald es zu .Orientation = xlDataField gelangt, ein „Laufzeitfehler 5...Ungültiger Prozeduraufruf oder ungültiges Argument“ ausgegeben. Ich muss auch sicherstellen, dass diese DataFields gemittelt und in einem bestimmten Zahlenformat vorliegen. Nichts, was ich tue, funktioniert und ich wäre für jede Anleitung/Lösung/Problemumgehung sehr dankbar! Unten finden Sie das Makro und einen Download-Link zur Datei zum Herumprobieren.
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
With pvtTable.CubeFields(i)
If .Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
.Orientation = xlDataField
'has to be averaged
'has to have number format of ##0.00
End If
End With
Next
Next
Dies ist eine Bearbeitung, die ich anhand des Makrorecorders versucht habe. Sie fügt das Feld in das Wertefeld ein, aber es gibt keine Möglichkeit, es in einen Durchschnitt zu ändern. Es listet einfach alle Werte als „1(Siehe Bild für eine visuelle Darstellung). Wenn ich zu .Function = xlAverage komme, erscheint die Meldung „Fehler 1004: Die Funktionseigenschaft der PivotField-Klasse konnte nicht festgelegt werden.“
If Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
With ActiveSheet.PivotTables(1)
.AddDataField ActiveSheet.PivotTables(1) _
CubeFields(cubName), _
"Average of " & cubName
End With
With ActiveSheet.PivotTables(1).PivotFields(cubName)
.Caption = "Average of " & cubName
.Function = xlAverage
End With
Wenn ich den Makrorekorder verwende, um dem Datenfeld etwas hinzuzufügen, erhalte ich Folgendes:
ActiveSheet.PivotTables("effRent_perBed_Pivot").CubeFields.GetMeasure _
"[effRent_perBed].[Jan-16]", xlSum, "Sum of Jan-16"
ActiveSheet.PivotTables("effRent_perBed_Pivot").AddDataField ActiveSheet. _
PivotTables("effRent_perBed_Pivot").CubeFields("[Measures].[Sum of Jan-16]"), _
"Sum of Jan-16"
With ActiveSheet.PivotTables("effRent_perBed_Pivot").PivotFields( _
"[Measures].[Sum of Jan-16]")
.Caption = "Average of Jan-16"
.Function = xlAverage
End With
Dies ist eine Kopie der Datei, an der ich arbeite. Sie zeigt die Roh-Pivot-Tabelle und dann das fertige Produkt. Ich muss dies für 3 Blätter tun, also muss ich jedes einzelne durchgehen. https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
Dies wird auch in anderen Foren gepostet (ich muss so schnell wie möglich so viele Aufmerksamkeit wie möglich darauf lenken) https://www.excelforum.com/excel-programmierung-vba-macros/1222588-cubefield-pivotfield-kann-nicht-als-datenfeld-in-vba-hinzugefügt-werden.html#post4856940
Antwort1
Ihre Frage könnte noch weiter ausgearbeitet werden. Sie sagen beispielsweise nicht, ob Sie eine PivotTable von Grund auf neu erstellen und dann Felder hinzufügen oder ob dies auf einer vorhandenen PivotTable ausgeführt wird, die möglicherweise bereits Felder enthält.
Jemand hat mich gerade auf dieProblemlösung mit Gummienten/Blogbeitrag, und ich werde Sie auch darauf hinweisen, weil es den Leuten die Beantwortung erleichtert, wenn sie alles Relevante wissen.
Beihttps://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-exceldort heißt es, die Syntax für .adddatafield sei AddDataField( Field , Caption , Function ), wobei die Funktion optional sei.
Was passiert also, wenn Sie dies versuchen?
If Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
With ActiveSheet.PivotTables(1)
.AddDataField ActiveSheet.PivotTables(1) _
CubeFields(cubName), _
"Average of " & cubName, _
xlAverage
End With
End if
Es gibt einige Probleme mit Ihrem ursprünglichen Codeblock. Ich verstehe nicht, warum Sie sowohl die CubeFields-Sammlung als auch eine Anzahl von Cubefields durchlaufen. Beispielsweise diesen Teil:
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
Wird das nicht einfach alles im Quadrat von Cubefields.count durchlaufen? Sie sollten in der Lage sein, das „For i = 1 To pvtTable.CubeFields.Count“-Bit wegzulassen und einfach direkt die cubField-Referenz zu verwenden.
Ich denke, der Grund, warum Ihr Code die Zusammenfassung nicht auf XLAverage setzen konnte, liegt darin, dass sich der Name effektiv ändert, sobald Sie dem Datenbereich ein Feld hinzufügen. Sie können dies sehen, wenn Sie das Datenfeld an Ort und Stelle lassen und diesen Code ausführen:
Sub Macro1()
'
' Macro1 Macro
'
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
Debug.Print cubField.Name
Next
End Sub
Dadurch werden die Namen aller Felder in das unmittelbare Fenster gedruckt (vorausgesetzt, Sie haben es geöffnet). Dort sehen Sie, dass Sie zwar ein Ergebnis für [effRent_perBed].[Manager] haben, dies jedoch NICHT das Datenfeld ist. Das Datenfeld wird so etwas wie [Measures].[Durchschnitt von effRent_perBed] sein.
Und deshalb ist Ihr Code fehlgeschlagen: Sie haben immer noch auf das Feld von Interesse verwiesen, als wäre es immer noch ein CubeField. Aber sobald Sie versuchen, es dem DataFields-Bereich hinzuzufügen, wird ein neues DataField erstellt undDas istdie, für die Sie die Aggregation ändern mussten.
Wie ich oben jedoch sagte, können Sie dies tun, wenn Sie das Datenfeld erstellen.