Microsoft Excel

Herbers Excel/VBA-Archiv

arrItemsOn mit Range aus Excel befüllen


Betrifft: arrItemsOn mit Range aus Excel befüllen von: Andrea
Geschrieben am: 27.07.2018 16:18:19

Hallo zusammen,

ich habe folgenden Code:

arrItemsOn = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

Dadurch wird das Monatsfeld einer Pivottabelle mit Werten (Januar - Dezember) befüllt.

Nun möchte ich aber, dass arrItemsOn mit dem Inhalt aus einer Excel-Zelle befüllen:

arrItemsOn = Range("B23").Value
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

Das funktioniert aber nicht, da arrItemsOn dann = "Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)" ist!?

Wie bekomme ich es hin, dass dort keine Anführungszeichen erscheinen?

Vielen Dank und beste Grüße

Andrea

  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: daniel
Geschrieben am: 27.07.2018 16:26:28

das wird aufwendiger.
du hast in der Zelle nur einen Wert und der ist text
am besten schreibst du in die Zelle nur die Werte, die ins Array sollen: 1, 2, 3, 4...

dann folgende Codezeile:

arrItemsOn = Split(range("B23").value, ", ")
wenn das nicht funktioniert, weil das Pivot-Feld die Zahlen 1-12 braucht und nicht die Texte "1" bis "12", dann noch dieser Zusatz:
arrItemsOn = Split(range("B23").value, ", ")
for i = 0 to ubound(arrItemsOn)
    arrItemsOn(i) = CLng(arrItemsOn(i))
Next
Gruß Daniel


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: Andrea
Geschrieben am: 27.07.2018 17:11:45

Hallo Daniel,

vielen Dank für den Input. Leider klappt es noch nicht.

Das Pivot-Feld benötigt in der Tat die Zahlen 1-12, so dass ich den 2. Code genommen habe.

Bei UBound(arrItemsOn) wird der Wert 2 ausgegeben, bei arrItemsOn(i) und CLng(arrItemsOn(i) allerdings "Index außerhalb des gültigen Bereichs". Vielleicht liegt es daran?

Gruß Andrea


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: daniel
Geschrieben am: 27.07.2018 17:18:21

Hi
Ubound(3) heißt, das du nur 4 Werte im Array hast (von Index 0 bis Index 3)
was steht denn in der Zelle drin?
hast du das Trennzeichen, welches du bei Split als zweiten Parameter angegeben hast, auch überall im Text verwendet, hierbei auch eventuelle Leerzeichen beachten.

Gruß Daniel


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: Andrea
Geschrieben am: 27.07.2018 17:29:52

Mein Code sieht wie folgt aus:

arrItemsOn = Split(Range("B23").Value, ", ")
For i = 0 To UBound(arrItemsOn)
arrItemsOn(i) = CLng(arrItemsOn(i))
Next
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

Und in B23 steht 1, 2, 3

VG Andrea


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: Daniel
Geschrieben am: 27.07.2018 18:03:48

HI
sieht prinzipell korrekt aus.
die Fehlermeldungen kann ich aus diesem Code nicht nachvollziehen.
die Schleife kannst du aber vorerst mal weglassen.
Der Variablentyp von arrItemsOn(i) wird durch die Schleife nicht geändert, sondern bleibt Text.
dh wenn dein Pivotfeld das Ergebnis der Schleife annimmt, dann geht's auch ohne Schleife.
wenn nicht, muss man es anders lösen.

Gruß Daniel


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: Andrea
Geschrieben am: 27.07.2018 18:33:12

Also so?

arrItemsOn = Split(Range("B23").Value, ", ")
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

Aber das geht auch nicht durch, da arrItemsOn dann nach wie vor "1, 2, 3" ist und nicht wie benötigt 1, 2, 3.

Funktioniert hat

arrItemsOn = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

Da wurden dann alle Monate ausgewählt.

Allerdings sehe ich gerade, dass bei

arrItemsOn = Array(4, 5, 6)
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = arrItemsOn

auch alle Monate ausgewählt werden!? Das soll natürlich nicht so sein. Jetzt hab ich also noch ein zweites Problem :-(

Weiß jemand Rat? Vielen Dank fürs Nachdenken.

VG Andrea


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: Andrea
Geschrieben am: 27.07.2018 18:37:40

Ich hatte das Häkchen vergessen, dass das Problem noch nicht gelöst ist.


  

Betrifft: Vergiss 'arrItemsOn=Split(Range("B23"),", ")' … von: Luc:-?
Geschrieben am: 28.07.2018 01:00:31

…und die nachfolgenden Operationen, die du möglicherweise vergessen hast, Andrea,
mache eine MatrixKonstante (in US-Notation) aus deinem Zellwert und verwende die Evaluate-Methode des Blattes, zB so:
Dim arrItemsOn As Variant
With ActiveSheet: arrItemsOn = .Evaluate("{" & .Range("B23") & "}"): End With

Dann sind alle in B23 aufgelisteten Ganzzahlen auch echte Zahlen und keine ZahlTexte "1", "2", "3", … wie sie regelmäßig durch die vbFkt Split erzeugt wdn, weshalb man die dann erst noch umwandeln, aber natürlich in einer anderen Variablen sammeln muss, denn der DatenTyp einer Variablen kann nicht nachträglich geändert wdn (Split liefert stets ein Array vom DatenTyp String, in einer Variant-Variable dann einen Variant vom Typ Array mit UnterTyp String).
Dein 2.Problem wirst du selber lösen können…‽
🙈 🙉 🙊 🐵 Morhn & heiWE, Luc :-?

„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …


  

Betrifft: AW: Vergiss 'arrItemsOn=Split(Range("B23"),", ")' … von: Luschi
Geschrieben am: 28.07.2018 11:35:54

Hallo Luc,

das ist ok, die Elemente des Arrays 'arrItemsOn' sind von Typ numerisch.
Eigentlich interessant, wie diese schon tot geglaubte Methode immer noch durch Excel-Vba
geistert und dabei so erfolgreich ist.

Gruß von Luschi
aus klein-Paris


  

Betrifft: Tja-ja, Totgeglaubte leben halt länger … von: Luc:-?
Geschrieben am: 29.07.2018 00:22:17

…(als man denkt), Luschi! ;-)
Im Grunde genommen ist diese Methode ja auch unverzichtbar, wenn man die Hilfe des Xl-FmlText-Inter­preters auch in VBA in Anspruch nehmen will bzw muss. Anderenfalls müsste man dessen Routinen je nach Bedarf ja selbst pgmmieren…
Schreibt man eine UDF für den Einsatz in einer Xl-Fml, hat man einen Teil dieser Hilfe ja automatisch (aber nicht alles!), in einer SubProzedur aber so gut wie keine von Xl (außer eben so).
Gruß + schöSo, Luc :-?


  

Betrifft: ...Und übrigens kann man mit 'Evaluate' … von: Luc:-?
Geschrieben am: 30.07.2018 02:38:31

…auch etwas erreichen, was anders nicht, kaum oder nur sehr umständlich möglich wäre, Luschi;
schreib doch mal eine kleine UDF, die die Fml einer BedingtFormat-Regel ausliest, und rufe die als FmlText mit Evaluate auf (am besten mit anderer UDF in einer ZellFml)…! ;-]
Tipp: Die Regel-Fml sollte auch eine Xl-Fkt enthalten, deren lokale Bezeichnung sich von der des Originals unterscheidet.
Luc :-?


  

Betrifft: AW: Vergiss 'arrItemsOn=Split(Range("B23"),", ")' … von: Andrea
Geschrieben am: 31.07.2018 09:04:03

Hallo Luc:-?, Daniel und die anderen,

ich befürchte ich verstehe nur noch Bahnhof! Ich bin allen Ratschlägen gefolgt, aber nichts führt zum Ziel. Wahrscheinlich weil ich dabei was falsch mache. Vielleicht erkläre ich nochmal genau, was mein Ziel ist.

Eigentlich möchte ich ja, dass die Einstellungen für Jahr und Monat aus einer Pivottabelle identisch in mehrere andere Pivottabellen übertragen werden. Aber das geht ja wohl leider nicht, wie ich mir hab sagen lassen :-(

Deshalb habe ich überlegt, dass der Anwender über Excel-Kontrollkästchen auswählt, welche Monate in den Pivot-Tabellen eingetragen werden sollen. Wenn er Januar auswählt wird in Zelle B11 eine 1 eingetragen, wenn er Februar auswählt in B12 eine 2 usw. Dazu habe ich in B11 eingetragen =WENN(A11=WAHR;1;"") usw. In A11 steht der Rückgabewert des Kontrollkästchens.

Wenn man also April, Mai und Juni auswählt, steht in der Spalte B nur 4 (in B14), 5 (in B15) und 6 (in B16). Und eben so wollte ich dann mein Array befüllen.

Mit dem Makrorekorder aufgezeichnet sieht die Array-Anweisung für den Monat (hier Januar) wie folgt aus:

            
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
                VisibleItemsList = Array("[Datum].[Monat].&[1]")
Wenn mehrere Monate ausgewählt sind (hier Januar und Februar):
            
Sheets("Einn. nach SB").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
        VisibleItemsList = Array("[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]")
Bisher ist es mir nur gelungen, entweder einen Monat einzustellen oder alle, aber nicht nur einzelne.
Vielleicht hat ja noch jemand die Nerven, einem offensichtlich völligen greenhorn weiterzuhelfen.
Vielen Dank.

Beste Grüße
Andrea


  

Betrifft: AW: arrItemsOn mit Range aus Excel befüllen von: daniel
Geschrieben am: 30.07.2018 15:57:31

HI
hast du schon mal in der Liste nach verschiedenen Monaten gefiltert und das mit dem Recorder aufgezeichnet?

wenn du das tust, solltest du sehen, dass der Text im Array etwas kompliziter aufgebaut ist, bei mir siehts ein Arrayeintrag so aus:
[Bereich].[Datum (Monat)].&[Jan]

dh die Schleife zum Umwandeln des Textes in B23 müsste so aussehen:

arrItemsOn = Split(range("B23").value, ", ")
for i = 0 to ubound(arrItemsOn)
    arrItemsOn(i) = "[Bereich].[Datum (Monat)].&[" & _
                    Format(CDate("1." & arrItemsOn(i)), "MMM") & _
                    "]"
Next
Aber wie gesagt, den genauen Text müsstest du erstmal mit Hilfe des Recorders rausfinden.

Gruß Daniel