Getpivotdata über Makro laufen lassen

Computer: Software & Programmierung Visual Basic
#1

Hallo zusammen,

ich mich jetzt mit der Funktion Pivotdatenzuordnen auseinander gesetzt und es funktioniert auch fantastisch. Nun habe ich das Problem, dass die ausgelesenen Daten in variabel große Tabellen eingelesen werden sollen. Ich weiß also nie genau, wie lang die Spalten sind. Nun wollte ich das Problem mit einem Makro lösen, aber leider funktioniert das nicht. Ich habe eine Tabelle, wo in Spalte I die Daten aus der Pivot Tabelle aus einem anderen Tabellenblatt eingelesen werden sollen. Das Auswahlkriterium steht in Spalte D.
Die Pivot Tabelle hat drei Spalten, Material, Materialkurztext, und Summe.

Ich hoffe Ihr könnt mir mal wieder helfen.

Vielen Dank im voraus,

LG Martina

#2

Grüezi Martina

ich mich jetzt mit der Funktion Pivotdatenzuordnen auseinander
gesetzt und es funktioniert auch fantastisch.

Na, das sag ich doch die ganze Zeit… :wink:

Nun habe ich das
Problem, dass die ausgelesenen Daten in variabel große
Tabellen eingelesen werden sollen. Ich weiß also nie genau,
wie lang die Spalten sind. Nun wollte ich das Problem mit
einem Makro lösen, aber leider funktioniert das nicht. Ich
habe eine Tabelle, wo in Spalte I die Daten aus der Pivot
Tabelle aus einem anderen Tabellenblatt eingelesen werden
sollen.

Dann müsste die Formel in diese Spalte geschrieben werden, denke ich.

Das Auswahlkriterium steht in Spalte D.

Dann musst Du den einen Parameter mit dieser Spalte verknüpfen.

Die Pivot Tabelle hat drei Spalten, Material,
Materialkurztext, und Summe.

Ich hoffe Ihr könnt mir mal wieder helfen.

Leider fehlen deiner Anfrage ein paar relevante Angaben:

  • Wie lautet denn die Formel genau mit der Du die Daten aus der PT holst?

  • Wo stösst Du auf Probleme beim Verknüpfen mit den Kriterien in Spalte D?

  • Wo liegt das Problem beim automatisieren?

  • Wieso willst/musst Du das Ganze denn mit VBA tun und nicht einfach die Formel per Doppelklick nach unten kopieren?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#3

Hallo Thomas,

die Formel steht in der Spalte. Hier die Formel:

=WENN(ISTFEHLER(PIVOTDATENZUORDNEN(„Lagerbestände“;‚Übersicht Lagerbestand‘!A3;„Material“;TEXT(D2;"#")));0:stuck_out_tongue_winking_eye:IVOTDATENZUORDNEN(„Lagerbestände“;‚Übersicht Lagerbestand‘!A3;„Material“;TEXT(D2;"#"))).

Das klappt auch alles sehr gut. Ich habe die Formel auch schon mit dem Makro Recorder aufgezeichnet und auch das klappt wunderbar. Das Problem ist, dass diese Listen mehrmals am Tag upgedatet werden und mehrere Kollegen damit arbeiten müssen. Es wäre also vom Vorteil, wenn man nicht immer die Formel in der Spalte herunterziehen müsste, sondern per Makro die Spalte je nach Länge ausfüllen könnte. Hier die aufgezeichnete Formel:

ActiveCell.FormulaR1C1 = _
„=IF(ISERROR(GETPIVOTDATA(“„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#""))),0,GETPIVOTDATA("„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#"")))"
Range(„I2“).Select
Selection.AutoFill Destination:=Range(„I2:I1000“)
Range(„I:I“).Select
ActiveWindow.SmallScroll Down:=-18
Range(„A2“).Select

Ich würde gerne die Angelegenheit mit dem Autofill automatisieren.

Ich hoffe, Du hast mein Anliegen verstanden.

LG Martina

#4

=WENN(ISTFEHLER(PIVOTDATENZUORDNEN(„Lagerbestände“;‚Übersicht
Lagerbestand‘!A3;„Material“;TEXT(D2;"#")));0:stuck_out_tongue_winking_eye:IVOTDATENZUORDNEN(„Lagerbestände“;‚Übersicht
Lagerbestand‘!A3;„Material“;TEXT(D2;"#"))).

Ich würde gerne die Angelegenheit mit dem Autofill
automatisieren.

Hallo Martina,

ungetestet, probiers mal so:

Option Explicit
'
Sub tt()
Dim Zei As Long
Zei = Cells(Rows.Count, 9).End(xlUp).Row
Range("I2").FormulaR1C1 = \_
"=IF(ISERROR(GETPIVOTDATA(""Lagerbestände"",'Übersicht Lagerbestand'!R[1]C[-8],""Material"",TEXT(RC[-5],""#""))),0,GETPIVOTDATA(""Lagerbestände"",'Übersicht Lagerbestand'!R[1]C[-8],""Material"",TEXT(RC[-5],""#"")))"
Range("I2").AutoFill Destination:=Range("I2:I" & Zei)
Range("A2").Select
End Sub

Gruß
Reinhard

#5

Hallo Reinhard,

vielen Dank, es hat wunderbar funktioniert. Könnte man jetzt noch die Passage: „Range(„I2“).FormulaR1C1“ durch eine Variable ersetzten und in eine If Schleife einbetten? Ich habe es schon mal versucht aber leider nicht hinbekommen. Dann würde die Formel nicht in der Tabelle stehen.

Vielen Dank im voraus.

LG Martina

#6

Grüezi Martina

vielen Dank, es hat wunderbar funktioniert. Könnte man jetzt
noch die Passage: „Range(„I2“).FormulaR1C1“ durch eine
Variable ersetzten und in eine If Schleife einbetten?

Kannst Du das bitte noch etwas näher erläutern?

Ich habe
es schon mal versucht aber leider nicht hinbekommen. Dann
würde die Formel nicht in der Tabelle stehen.

Soll das heissen, dass Du nicht eine Formel sondern nur den Wert deren Berechnung in den Zellen haben möchtest?
Da machts Du am besen so, dass Du die Formel überall reinschreiben lässt und dann die Formeln durch die Werte ersetzen lässt.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

Hallo Martina,

vielen Dank, es hat wunderbar funktioniert.

sehr schön.

Könnte man jetzt
noch die Passage: „Range(„I2“).FormulaR1C1“ durch eine
Variable ersetzten und in eine If Schleife einbetten? Ich habe
es schon mal versucht aber leider nicht hinbekommen. Dann
würde die Formel nicht in der Tabelle stehen.

In einem (großen) Zellbereich alle Zellen/zeilen einzeln mit einer Vba-Schleife abzuklappern ist sehr beträchtlich langsamer als Excelformeln zu benutzen.

Wenn es nur darum geht die Formeln „danach“ wieder zu entfernen, füge die fettgedruckte Codezeile ein.

Sub tt()
Dim Zei As Long
Zei = Cells(Rows.Count, 9).End(xlUp).Row
Range(„I2“).FormulaR1C1 = _
„=IF(ISERROR(GETPIVOTDATA(“„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#""))),0,GETPIVOTDATA("„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#"")))"
Range(„I2“).AutoFill Destination:=Range(„I2:I“ & Zei)
Range(„I2:I“ & Zei).value=Range(„I2:I“ & Zei).value
Range(„A2“).Select
End Sub

Nochmal grundsätzlich zum Code, vielleicht habe ich da einen Planungsfehler drinnen.
Nach der Codezeile
Zei = Cells(Rows.Count, 9 ).End(xlUp).Row
hat man in der Variablen Zei die Zeilennummer der untersten gefüllten Zelle in der 9-ten Spalte, also I.

Trage anstatt der „9“ ggfs. eine andere Zahl ein, 1=Spalte A, 2=Spalte B usw.
Aufgrund von „Zei“ weiß Vba bis zu welcher untersten Zeilennummer es da Berechnungen durchführen soll.
Und in der I-Spalte soll dann natürlich in den Zellen unterhalb von Zeilennummer „zei“ nichts stehen.

Das kann man so lösen:

Sub tt()
Dim Zei As Long
columns(9).clearcontents
Range(„I1“).value="Überschrift"
Zei = Cells(Rows.Count, 9).End(xlUp).Row
Range(„I2“).FormulaR1C1 = _
„=IF(ISERROR(GETPIVOTDATA(“„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#""))),0,GETPIVOTDATA("„Lagerbestände“",‚Übersicht Lagerbestand‘!R[1]C[-8],"„Material“",TEXT(RC[-5],""#"")))"
Range(„I2“).AutoFill Destination:=Range(„I2:I“ & Zei)
Range(„I2:I“ & Zei).value=Range(„I2:I“ & Zei).value
Range(„A2“).Select
End Sub

Gruß
Reinhard

#8

Hallo Reinhard,

nochmals vielen Dank, auch die Erweiterung funktioniert wunderbar. Die 9 hatte ich schon durch eine 4 ersetzt. Wahrscheinlich hast Du recht und so geht es schneller. Ich danke Dir noch einmal für die Hilfe.

LG Martina

#9

Hallo Thomas,

auch Dir vielen Dank für die Antwort. Reinhard hat mein Problem gelöst und nun funktioniert alles einwandfrei.

Danke Euch noch mal.

LG Martina