Live-Forum - Die aktuellen Beiträge
Datum
Titel
08.05.2024 11:33:57
Print Area auswählen
08.05.2024 11:27:06
Bestimmten Wert von einer Excel Liste übertragen
08.05.2024 11:11:10
Sortieren_Leerzeile
08.05.2024 10:55:01
Rückgängig machen nicht mehr möglich
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1944to1948
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1944to1948
1944to1948
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Makro optimieren.

Makro optimieren.
09.09.2023 14:38:11
Christian
Hallo,

ich versuche seit neuestem ein wenig mir VBA beizubringen und habe mir das unten stehende Makro zusammengebaut, mithilfe von Google, Recorder aber auch diesem Forum.

Soviel soll es eigentlich gar nicht machen, ich habe eine intelligente Tabelle, in dieser soll es eigentlich nur 4 Formeln berechnen und die Tabelle neu sortieren.

Aber irgendwie kommt es mir so vor, sagt jedenfalls mein bauchgefühl dass ich es mir gerade im Bereich des Sortierens viel zu umständlich gemacht habe und es würde mich freuen wenn sich jemand das ganze mal anschaut, ob sich das noch optimieren lässt.

Zwei Infos noch.

1. Die beiden NumberFormats in Spalte B habe ich gemacht, damit die Texte so übernommen werden wie sind und nicht z.b. aus 12/12/12 das Datum 12.12.2012 gemacht wird.

2. Wenn ihr euch jetzt die Frage stellt, warum ich überhaupt Formeln per Makro berechne und nicht direkt. Wegen der Berechnungszeit, jedesmal bei jeder Änderung an der Tabelle 5 Sekunden warten bis alle Formeln berechnet sind. Das Makro berechnet sie auf Wunsch und sonst nicht. Ich möchte das auch so beibehalten dass sie per Makro berechnet werden und nicht auf deaktivierung der Formelberechnung in den Excel Optionen ausweichen.

Danke
Christian

PS: Das Makro funktioniert wie es soll, es geht mir nur um Optimierung, nicht um Fehlersuche. 

Private Sub Liste()

        Dim loLetzte As Long

        Application.ScreenUpdating = False

        

    With Worksheets("Liste").ListObjects(1).DataBodyRange

             

       With .Columns(2)

       .NumberFormat = "General"

       .FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)"

       .NumberFormat = "@"

       .Formula = .Value2

       End With

       

       With .Columns(3)

       .FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!E:E;"""";0;1)"

       .Formula = .Value2

       End With

       

       With .Columns(5)

       .FormulaLocal = "=XVERWEIS(D2;Leute!B:B;Leute!D:D;"""";0;1)"

       .Formula = .Value2

       End With

       

       With .Columns(6)

       .FormulaLocal = "=WENN(XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1)="""";"""";XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1))"

       .Formula = .Value2

       End With

        

       End With

        

       With Worksheets("Liste")

       loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row

            .Sort.SortFields.Clear

            .Sort.SortFields.Add Key:=.Range("C2:C" & loLetzte), SortOn:=xlSortOnValues, _

            Order:=xlDescending, DataOption:=xlSortNormal

            .Sort.SortFields.Add Key:=.Range("F2:F" & loLetzte), SortOn:=xlSortOnValues, _

            Order:=xlDescending, DataOption:=xlSortNormal

            With .Sort

                .SetRange Range("A2:F" & loLetzte)

                .Header = xlNo

                .MatchCase = False

                .Orientation = xlTopToBottom

                .SortMethod = xlPinYin

                .Apply

            End With

       End With

       

            Application.CutCopyMode = False

            Application.ScreenUpdating = True

     End Sub

37
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro optimieren.
09.09.2023 15:19:25
Oberschlumpf
Hi Christian,

ohne deine Datei zu kennen, versuch es mal mit diesem Code (ungetestet) 


Sub sbStart()



    Dim lloCol As Long



    Application.ScreenUpdating = False



        For lloCol = 2 To 6

            Liste lloCol

        Next



    Application.CutCopyMode = False

    Application.ScreenUpdating = True



End Sub



Private Sub Liste(ByVal spalte As Long)

        

    Dim loLetzte As Long, lstrFormula As String



    Select Case spalte

        Case 2

            lstrFormula = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)"

        Case 3

            lstrFormula = "=XVERWEIS(A2;Filme!B:B;Filme!E:E;"""";0;1)"

        Case 5

            lstrFormula = "=XVERWEIS(D2;Leute!B:B;Leute!D:D;"""";0;1)"

        Case 6

            lstrFormula = "=WENN(XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1)="""";"""";XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1))"

    End Select

    

    If lstrFormula > "" Then

        With Worksheets("Liste").ListObjects(1).DataBodyRange

                 

           With .Columns(spalte)

           .NumberFormat = "General"

           .FormulaLocal = lstrFormula

           .NumberFormat = "@"

           .Formula = .Value2

           End With

           

           With Worksheets("Liste")

           loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row

                .Sort.SortFields.Clear

                .Sort.SortFields.Add Key:=.Range("C2:C" & loLetzte), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

                .Sort.SortFields.Add Key:=.Range("F2:F" & loLetzte), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

                With .Sort

                    .SetRange Range("A2:F" & loLetzte)

                    .Header = xlNo

                    .MatchCase = False

                    .Orientation = xlTopToBottom

                    .SortMethod = xlPinYin

                    .Apply

                End With

            End With

        End With

    End If

    

End Sub

Gestartet wird nun alles mit Sub sbStart.
Darin enthalten ist eine For/Next-Schleife, die von 2 bis 6 (die Spalten, um die es dir geht) hochzählt.

In der Schleife wird mit Übergabe des jeweiligen Werts der Schleifenvariable deine Sub Liste aufgerufen.

Zuerst in Sub Liste wird nun der übergebene Wert aus Sub sbStart geprüft.
Nur wenn Wert einer von den "Gesuchten" ist, erhält die neue Variable lstrFormula einen Wert, nämlich den für die gerade übergebene Spalte den richtigen Formelstring.

Und nur, wenn lstrFormula einen Wert erhalten hat, wird dein nun verkürzter/optimierter Code durchgeführt.
Alle Spalten, die in deinem alten Code nicht bearbeitet wurden, werden auch hier übersprungen.

Hilfts?
Nein? Dann zeig bitte per Upload eine Bsp-Datei mit Bsp-Daten und deinem Code.

Ciao
Thorsten

Anzeige
AW: Makro optimieren.
10.09.2023 19:44:40
Oberschlumpf
naaaa??? gibbs denn auf meine letzte Antwort auch noch ma bitte ne Antwort???
Makro optimieren.
12.09.2023 18:29:22
daniel
HI
viel ist an dem Code eigentlich nicht zu optimieren.

ein Fehler ist auf jeden Fall noch drin, bei .SetRange Range("A2:F" & loLetzte) die Refererenz auf das Tabellenblatt.
den Punkt kann man hier nicht verwenden, weil die aktuelle WITH-Klammer nicht auf das Tabellenblatt referenziert, sondern auf das .Sort-Objekt.
also entweder das Tabellenblatt hier noch einfügen, oder auf die innere WITH-Klammer verzichten.
Dann muss man da zwar überall das "Sort" davor schreiben, aber das ist immer gleich und du kannst hier auch vor das Range den Punkt setzen.
auch für den SortKey braucht man eigentlich nur die Spalte bzw eine Zelle dieser Spalte, dh ein Key:=Range("C2") reicht auf jeden Fall.

Wenn man die Sortierungsprogrammierung wirklich vereinfachen will, dann sollte man die Programmierung, wie sie bis Excel 2003 verwendet wurde, anwenden.
da geht das in einer einzigen Zeile:
wenn man mehr als 3 Sorterkriterien hat, kann man einfach in mehreren Schritten sortieren, auch dass ist immer noch einfacher als die neue Programmierung.
außerdem kann man hier auch einfacher das Listobjekt als referenz verwenden. 

With Worksheets("Liste").ListObjects(1).DataBodyRange

    .Sort Key1:=.Cells(1, 3), Order1:=xldescending, Key2:=.Cells(1, 6), order2:=xldescending, Header:=xlno, orientation:=xltoptobottom

End with



als weitere Optimierung würde ich im oberen Teil die Formeln nicht in .FormulaLocal, sondern in .FormulaR1C1 schreiben.
Das hat den Vorteil, dass der Code dann unabhängig von der Ländervariante funktioniert, und du kannst die relativen Zellbezüge auch relativ angeben.
Das solltest du hier auch tun, denn du weißt ja nicht, ob dein Listobjekt wirklich in Zeile 1 mit der Überschrift beginnt (Daten in Zeile 2), oder ob es verschoben ist.
Sollte das der Fall sein, müssetst du das "=XVERWEIS(A2..." in "=XVERWEIS(A3..." abändern.
um das zu vermeiden, verwendet man (dann das englische) "=XLookUP(RC1...), das heißt dann: Zellen in Spalte 1 in der selben Zeile, und da spielt es dann keine Rolle, welche Zeile das letztendlich ist.

also ums nochmal zusammenzufassen:
1. Sortieren wenn möglich in der alten Excel2003-Schreibweise programmieren
2. auch hier die Referenz auf das Listobjekt verwenden
3. Formeln in R1C1 schreiben.

eine kleinere Optimierung wäre, dass du die Zellen zuerst mit Formeln befüllst und dann gemeinschaftlich in Werte wandelst, dass lässt den Code kompakter werden, weil du dann die zusätzlichen WITH-Klammern nicht brauchst.
Achtung: Wenn die Spalten 1 und 4 Formeln enthalten, die erhalten bleiben müssen, darfst du das nicht machen: 

With Worksheets("Liste").ListObjects(1).DataBodyRange

       .Columns(2).NumberFormat = "General"

       .Columns(2).FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)"

       .Columns(2).NumberFormat = "@"

       .Columns(3).FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!E:E;"""";0;1)"

       .Columns(5).FormulaLocal = "=XVERWEIS(D2;Leute!B:B;Leute!D:D;"""";0;1)"

      .Columns(6).FormulaLocal = "=WENN(XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1)="""";"""";XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1))"

      .Formula = .Value2

End With


gruß Daniel

Anzeige
Makro optimieren.
12.09.2023 18:55:19
Christian
Hallo Daniel,

danke erstmal für deine Ansätze. Ich gehe einfach mal Punkt für Punkt durch.

1. ich verstehe nicht ganz warum es einen Fehler mit der Referenz aufs Tabellenblatt gab und dennoch das Makro wie es sollte funktioniert hat.

2. Habe ich dich richtig verstanden, es gibt 2 verschiedene Möglichkeiten mit VBA zu sortieren und bei der neuen Variante muss ich all die Dinge angeben die ich angegeben habe und kann auf nichts davon verzichten?

3. mit dem FormulaR1C1 tue ich mich irgendwie schwer. Das habe ich mal in einem anderen Makro probiert, das Umwandeln habe ich ja noch im Direktbereich hinbekommen. Aber dann wollte ich irgendwann mal einen Zellbezug ändern und habe nicht mehr durchgeblickt. Im Endeffekt habe ich die Formeln neu erstellt und erneut umgewandelt, da ich auch keine Möglichkeit gefunden hatte ?Replace(selection.formular1c1, """", """""") umzukehren

4. Die Änderung im Formelbereich habe ich übernommen. 

Private Sub Liste()

        Dim loLetzte As Long

        Application.ScreenUpdating = False

        

 With Worksheets("Liste").ListObjects(1).DataBodyRange

       .Columns(2).NumberFormat = "General"

       .Columns(2).FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)"

       .Columns(2).NumberFormat = "@"

       .Columns(3).FormulaLocal = "=XVERWEIS(A2;Filme!B:B;Filme!E:E;"""";0;1)"

       .Columns(5).FormulaLocal = "=XVERWEIS(D2;Leute!B:B;Leute!D:D;"""";0;1)"

      .Columns(6).FormulaLocal = "=WENN(XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1)="""";"""";XVERWEIS(D2;Leute!B:B;Leute!C:C;"""";0;1))"

      .Formula = .Value2

End With

        

       With Worksheets("Liste").ListObjects(1).DataBodyRange

    .Sort Key1:=.Cells(1, 3), Order1:=xlDescending, Key2:=.Cells(1, 6), order2:=xlDescending, Header:=xlNo, Orientation:=xlTopToBottom

       End With

       

            Application.CutCopyMode = False

            Application.ScreenUpdating = True

     End Sub


Der Code scheint so erstmal zu funktionieren. Aber eine Verständnisfrage habe ich dann doch noch Cells(1, 3) ist doch erstmal die Celle C1 dachte ich und selbst wenn damit die erste Zeile mit Daten gemeint ist, würde das doch heißen dass in Zeile 1 eine Überschrift existiert...

Naja auf jeden Fall vielen Dank für die Mühe und Erklärungen
Christian

Anzeige
Makro optimieren.
12.09.2023 20:27:10
Daniel
1. Range ohneTabellenblatt davor referenziert auf das aktive tabellenblatt, wenn der Code in einem allgemeinen Modul steht und auf das Tabellenblatt des Moduls, wenn der Code in einem Tabellenblattmodul steht.
Dh wenn man Range mit und ohne Tabellenblattangabe davor hat, funktioniert das ganze nur dann, wenn das richtige Tabellenblatt aktiv ist, aber nicht wenn ein anderes aktiv ist.

2. Man muss auch bei der neuen Programmierweise nicht alle Parameter angeben und kann die optionalen weglassen. Excel verwendet dann die zuletzt gemachte Einstellung.
Die alte Programmierweise ist einfacher, weil sie maximal drei Keys verwalten kann und keine variable Anzahl beherrschen muss, wie sie die aktuelle Version bietet.

3. R1C1 ist auch nichts anderes, als Spaltennummern statt Spaltenbuchstaben. Wer das nicht kapiert, wirds sowieso schwer beim Programmieren haben, weil man das öfters braucht (Cells(x, y), Arrays)
Mann muss sich beim Schreiben der Formel in Excel nur angewöhnen, möglichst viele Bezüge absolut zu setzen und nur die relativ zu lassen, die zwingend relativ sein müssen, dann wird R1C1 auf einmal viel übersichtlicher und einfacher zu lesen als wenn man alles relativ lässt.

4. Man kann Cells(1, 3) nicht nur an ein Tabellenblatt anhängen, sondern auch an einen anderen Zellbereich, also sowas geht:
Range("F15:M36").Cells(1, 3)
Und dann bedeutet das "die Zelle in der ersten Zeile und dritten Spalte dieses Bereichs", was im Beispielfall dann die Zelle H15 auf dem Tabellenblatt wäre.

Gruß Daniel
Anzeige
Makro optimieren.
12.09.2023 20:53:52
GerdL
Moin,
den XVerweis kennt mein Excel noch nicht. 






Application.Calculation = xlCalculationManual



With Worksheets("Liste").ListObjects(1).DataBodyRange

             

       With .Columns(2)

       .NumberFormat = "General"

       '.FormulaLocal = "=SVERWEIS(A2;Filme!B:C;2;FALSCH)"

       .FormulaR1C1 = "=VLOOKUP(RC1,Filme[[Const]:[Title]],2,FALSE)"  (R= verwendete Zeile ; C1 = Spalte 1=A)

       .NumberFormat = "@"

       .Formula = .Value2

       End With

'.........................

'........................

End With



Application.Calculation = xlCalculationAutomatic

Gruß Gerd
Anzeige
Makro optimieren.
12.09.2023 21:05:44
Christian
Hallo Gerd,

sei mir bitte nicht böse, wenn ich das erst morgen ausprobiere, ich bin froh jetzt mal das Laptop auszumachen, war ein langer Tag.

Übrigens was kleines am Rande, so einleuchtend es auch ist, dass [Const]:[Title] funktioniert. Gerade weil ich ja geschrieben habe dass es sich im Original im Blatt Filme um eine PQ Abfrage handelt. Ich sehe das zum ersten mal in der Matrix im SVERWEIS. Mag aber vielleicht auch daran liege dass ich seit es ihn gibt den XVERWEIS nutze. Irgendjemand meinte mal zu mir, er ist schneller.

Danke auch dir schonmal, ich melde mich morgen, versprochen.

Christian
Anzeige
Makro optimieren.
13.09.2023 10:17:21
Christian
Hallo Gerd,

es funktioniert. Aber wie ich bereits sagte, ich schaffe es zwar über das Direktfenster in R1C1 umzuwandeln aber tue mich zu schwer damit dann mal Zellbezüge zu ändern, wenn es diesen Bedarf gibt.

Gruß und danke
Christian
Makro optimieren.
13.09.2023 10:33:19
daniel
hi
der Vorteil von R1C1 ist, dass man Zellbezüge weniger oft ändern und anpassen muss als mit A1-Adressen.
denn mit R1C1 kann man relative Zelladressen auch relativ beschreiben, während A1-Adressen immer absolut beschrieben sind, auch die relativen Bezüge.
Wie gesagt, wenn man sich angewöhnt, beim Erstellen möglichst viele Bezüge absolut zu setzen und nur die relativ zu lassen, die auch zwingend relativ sein müssen, dann werden Formeln in R1C! wesentlich einfacher und übersichtlicher.
Gruß Daniel
Anzeige
Makro optimieren.
12.09.2023 20:59:45
Christian
nochmalzu R1C1 das Prblem war eher dann solche Sachen wie wenn dann da steht C[-8] ja welche Spalte ist denn jetzt die 8. davor.

Solche Probleme waren das eher, die mich dann irgendwann genervt hatten. Aber du hast schon recht, ich bin nicht derjenige der mit Programmieren mal viel Geld verdienen wird. Alles andere habe ich denke ich verstanden. Danke für die Erklärung
Makro optimieren.
11.09.2023 08:02:02
Christian
Hallo Thorsten,

selbstverständlich gibt es die. Es war einfach ein Wochenende, an dem alles anders kam als geplant, ich wurde überall gebraucht, nur nicht am Laptop.

Ich habe jetzt, ich hoffe ich habe dich richtig verstanden folgendes daraus gemacht: 

If lstrFormula > "" Then

        With Worksheets("Liste").ListObjects(1).DataBodyRange

                 

           With .Columns(spalte)

             If spalte = 2 Then

                .NumberFormat = "General"

           End If



           .FormulaLocal = lstrFormula

           .NumberFormat = "@"

           .Formula = .Value2

           End With

           

           With Worksheets("Liste")

           loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row


Das hat dazu geführt, dass Spalte B richtig berechnet wird, in Spalte C und F zwar der richtige Wert steht, jedoch als Zahl anstatt als Datum und was ich überhaupt nicht nachvollziehen kann, in Spalte E steht jetzt durchgängig =XVERWEIS(D2;Leute!B:B;Leute!D:D;"";0;1) als Text.

Wenn ich stattdessen das End if nach .Formula = .Value2 schreibe, bleiben die Spalten C E und F leer.

Ich weiß, du hast mich darum gebeten, in diesem Fall eine Beispieldatei hochzuladen.
Ich bin jetzt etwas ratlos was ich tun soll. Kurz erklärt, es gibt 2 CSV Dateien, die mit PQ in den Blättern Filme und Leute hochgeladen werden und die XVerweis Formeln suchen sich dann daraus die Daten raus. Du kannst dir ja denken, wenn ich von mehreren Sekunden Berechnungszeit rede, dass das ein Konstrukt ist, was die Obergrenze für Dateigrößen in diesem Forum ziemlich sprengt.

Daher meine Frage, reichen auch exemplarisch ein paar Zeilen aus jedem der 3 Blätter ohne die CSV Dateien und PQ Abfragen? Den XVerweis Formeln und dem Makro sollte es ja egal sein, ob die Daten aus einer normalen Tabelle oder einer PQ Abfrage kommen oder?

Alternativ dazu habe ich auch eine Lösung gefunden, die denke ich eher unschön ist, aber funktioniert.

Ich habe in deinem ursprünglichen Makro die Zeile 

If lstrFormula > "" Then


in 

If lstrFormula = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)" Then


geändert und sonst alles unverändert gelassen. Das hatte funktioniert.

Wie machen wir jetzt weiter?

Danke und sorry nochmal für die wenige Zeit am Wochenende
Christian
Anzeige
AW: Makro optimieren.
12.09.2023 12:14:51
Oberschlumpf
Hi Christian,

eigtl hast du mich richtig verstanden, und den Code so geändert, wie von mir vorgeschlagen.

Mir fällt erst jetzt auf, dass du außer...

.NumberFormat = "General"

...zusätzlich noch...

.NumberFormat = "@"

...verwendest?

Also, in deinem "Originalcode standen somit...
.NumberFormat = "General"
.NumberFormat = "@"
...direkt untereinander

Was passiert denn, wenn du die Zeile mit .NumberFormat = "@" einfach mal löschst?

Somit würden (sollten??? :-) ) jetzt nur noch die Inhalte in Spalte B formatiert werden, alle anderen Werte müssten ihre Ursprungsformate beibehalten, oder???

Ja, stimmt, Bsp-Dateien SIND IMMER hilfreicher, als nur zu erraten, was passt oder was nich.
Aber ich hab die "Ausnahme" gefunden - in diesem Fall hilft mir eine Bsp-Datei doch nicht weiter, weil ich noch MSO 2016 nutze, diese Version aber den XVerweis() nicht kennt.

Klappts denn jetzt?

Ciao
Thorsten
Anzeige
Makro optimieren.
12.09.2023 14:24:50
Christian
Hallo Thorsten,

leider nicht, das .NumberFormat = "@" hatte ich eingefügt, weil sonst beim Wiederholen des Makros nur Murks rauskam.
Das Ganze hatte nur funktioniert, wenn beim Start des Makros dieses Format da war, keine Ahnung weshalb.

Aber das Ganze kann doch eigentlich nur damit zu tun habe, dass das Makro denkt, es müsste jede Spalte formatieren.
Die eigentlichen Formate müssten doch alle passen.

Das schließe ich daraus, dass es funktioniert, wenn ich If lstrFormula > "" Then in If lstrFormula = "=XVERWEIS(A2;Filme!B:B;Filme!C:C;"""";0;1)" Then ändere und sonst alles so lasse wie in deinem allerersten Vorschlag.

Daher meine Frage kann man dann nicht direkt anstatt If lstrFormula > "" Then If spalte = 2 Then abfragen?

Gruß
Christian
AW: Makro optimieren.
12.09.2023 14:47:12
Oberschlumpf
Hi Christian,

a) dein gewünschtes If lstrFormula > "" Then If spalte = 2 Then ist doch schon in... 
If lstrFormula > "" Then

        With Worksheets("Liste").ListObjects(1).DataBodyRange

                 

           With .Columns(spalte)

             If spalte = 2 Then

...enthalten. Mit den übrigen Zeilen wird nur der Bezug auf die intelligente Tabelle innerhalb der Tabelle ("Blatt") und zusätzlich auf die gerade betroffene Spalte festgelegt, und das ist auch so erforderlich.

b) ok, lass die Zeile mit .NumberFormat = "@" stehen

c) damit nun nicht mehr die Zahlen eines Datums, sondern die für uns gebräuchliche Ansicht des Datums erscheint, änder mal diesen Code... 


With .Columns(spalte)

    If spalte = 2 Then

        .NumberFormat = "General"

    End If



    .FormulaLocal = lstrFormula

    .NumberFormat = "@"

...um in... 


With .Columns(spalte)

    .NumberFormat = "@"

    If spalte = 2 Then

        .NumberFormat = "General"

    End If

    If spalte = 3 Or spalte = 6 Then

        .NumberFormat = "TT.MM.JJJJ" 'vllt auch "DD.MM.YYYY" oder ähnlich; musst du ausprobieren

    End If

    .FormulaLocal = lstrFormula

So wird nun in dieser Reihenfolge...
...zuerst jedes Format in "@" geändert
...erst dann reagiert der Code mit anderen Zell-Formatierungen, wenn Spalte B oder Spalte C oder F betroffen sind.

Wenn das jetzt auch nich hilft - weiß ich nich ewiter...und bin raus....oder du erstellst eine "neue" Bsp-Datei, die nur Formeln enthält, die auch MSO 2016 kennt.

Hilfts?

Ciao
Thorsten
Datei
12.09.2023 15:21:46
Christian
Hallo Thorsten,

klar die XVerweise in Sverweise umwandeln ist sicher das geringste Problem. Hier die Datei, hab sie aber erstmal ohne die PQ Abfragen und CSV Dateien gemacht und mit meinem ursprünglichen Makro.
Führ das Makro einmal aus, dann siehst du was passiert.

Gruß
Christian

https://www.herber.de/bbs/user/162803.xlsm
AW: Datei
12.09.2023 15:39:54
Oberschlumpf
Hi Christian,

nach Ausführen deines Codes seh ich das hier:
Userbild

in den Spalten 3 UND 6 werden die Datumswerte so angezeigt, wie wir es gewohnt sind - is also alles richtig....was den Code betrifft.
An deinem Computer, bzw Excel-Einstellungen passt irgdwas nich.

Oder........
Hast du vielleicht DEN Code angewendet, der, wie du 2 oder 3x erwähnt hast, auch wie gewünscht funktioniert - und du hast NICHT den Code verwendet, mit dem die Datumswerte nur als Zahlen gezeigt werden???????

Wenn ja, dann versteh ich deine Antwort mit Datei nicht - dann verwende doch deinen Code - der funktioniert doch.

Oder was genau wolltest du mir nun zeigen?

Ciao
Thorsten
Datei
12.09.2023 15:49:45
Christian
Hallo Thorsten,

der Code funktioniert einwandfrei, keine Frage.
Wie ich bereits im ersten Beitrag sagte:

Aber irgendwie kommt es mir so vor, sagt jedenfalls mein bauchgefühl dass ich es mir gerade im Bereich des Sortierens viel zu umständlich gemacht habe und es würde mich freuen wenn sich jemand das ganze mal anschaut, ob sich das noch optimieren lässt.

Mit optimieren hatte ich gemeint, ob ich es mir zu umständlich gemacht habe.

Wie ich ebenfalls gesagt hatte:

PS: Das Makro funktioniert wie es soll, es geht mir nur um Optimierung, nicht um Fehlersuche.

Du hast recht ich kann dieses Makro genauso weiterverwenden, aber das war nicht mein Anliegen. Mein Anliegen war von euch zu lernen, wie man es auch hätte besser machen können.

Gruß
Christian
bitte wer anders
12.09.2023 16:36:46
Oberschlumpf
Hi,

in einigen deiner Beiträge erwähntest du gleichzeitig! (jetzt mit meinen Worten)

"Datumswerte werden nur als Zahlen dargestellt"
"bei Verwendung von .Formula = "=XVerweis(...." (oder so ähnlich) funktioniert alles


Da beides gleichzeitig erwähnt wurde, sagte mein Bauchgefühl dann immer wieder, dass du mithilfe - meines Codes - die Darstellung nur als Zahlen "weghaben" wolltest.

Wenn du schlussendlich nuuur noch wissen wolltest, ob und wie man vllt noch den Sortier-Code optimieren könnte, warum hast du dann nicht - genau nur diese - Frage gestellt?

Ciao
ich
bitte wer anders
12.09.2023 17:17:02
Christian
Ja das habe ich erwähnt, aber bezogen auf dein Vorschlag das makro anzupassen
Und die andere frage
12.09.2023 17:21:31
Christian
Ja das habe ich erwähnt, aber bezogen auf dein Vorschlag das makro anzupassen. Das vor allem der sortiercode optimierungsfähig wäre war eine Vermutung von mir. Ich hatte nach dem ganzen Code gefragt für den Fall dass ich mich da täusche und auch der Rest zu umständlich programmiert wurde von mir. Es tut mir leid dass es da solche Missverständnis gab.

Und die andere frage
12.09.2023 17:43:19
ralf_b
villeicht hilft es ja wenn du deinen Formeln nicht die komplette Spalte zum rechnen gibst. Also nicht Filme!B:B;Filme!C:C sondern beispielhaft Filme!B2:B1000;Filme!C2:C1000

und wenn du die Automatische Berechnung des Blattes abschaltest und erst wieder nach dem letzten Formelsetzen aktivierst.
kopf kratz
12.09.2023 18:29:56
Christian
Hallo Ralf,

wie gesagt ich bin als VBA Anfänger einzustufen, aber ich dachte die Zeile With Worksheets("Liste").ListObjects(1).DataBodyRange würde das ganze auf den genutzten Bereich der intelligenten Tabelle beschränken. Bitte korrigier mich wenn ich falsch liege.

Gruß
Christian
kopf kratz
12.09.2023 18:51:58
ralf_b
deine formeln schreibst du in eine intelligente tabelle aber die formel selbst sucht in der ganzen Spalte. ca. 1 million zellen
kopf kratz
12.09.2023 19:01:52
Christian
ok, sorry da stand ich auf dem Schlauch, jetzt weiß ich was du meinst.

Aber dann habe ich mal eine Frage aus deiner Erfahrung was aus deiner Sicht bei größeren Datenmengen mehr Sinn macht.

Ein festes Ende vorgeben, wie du es vorgeschlagen hast, mit der Gefahr dass man es irgendwann wenn es zu viele Zeilen werden nochmal anpassen muss

oder

mit Anzahl2 (da es keine Leerzellen gibt) das tatsächliche Ende in einer Nebenrechnung in einer seperaten Zelle bestimmen und dann mit INDEX das Ganze zusammensetzen?

Gruß
Christian
kopf kratz
12.09.2023 19:05:56
ralf_b
du arbeitest mit vba, da ist es ein Klacks die letzte benutzte Zeile zu bestimmen bzw bei intelligenten Tabellen deren Grenzen zu nutzen.
geht natürlich auch
12.09.2023 19:09:44
Christian
aber trotzdem wäre es interessant gewesen mal eine Antwort auf diese Frage in Bezug auf Rechenzeit bei großen Datenmengen zu haben
geht natürlich auch
12.09.2023 20:08:27
ralf_b
ja das wäre interessant, Ich kann da nur theoretisch weiterhelfen. Meine Antwort: Array's, statt Zellbezüge
Was habe ich nun jetzt wieder falsch gemacht?
13.09.2023 10:50:27
Christian
Hallo, ich nochmal

ich habe jetzt versucht noch Ralfs Vorschlag umzusetzen und das Makro das Ende der Blätter Leute und Filme bestimmen zu lassen aber er meckert schon bei der Zeile 
LetzteF = Worksheets("Filme").Cells(.Rows.Count, 1).End(xlUp).Row


Würdet ihr bitte auch da nochmal draufschaun was da jetzt alles schiefläuft? Danke 

Sub Liste()

        Dim LetzteF As Long

        Dim LetzteL As Long

        Application.ScreenUpdating = False

        

 LetzteF = Worksheets("Filme").Cells(.Rows.Count, 1).End(xlUp).Row

 LetzteL = Worksheets("Leute").Cells(.Rows.Count, 1).End(xlUp).Row

        

 With Worksheets("Liste").ListObjects(1).DataBodyRange

       .Columns(2).NumberFormat = "General"

       .Columns(2).FormulaLocal = "=XVERWEIS(A2;Filme!B2:B" & LetzteF & ";Filme!C2:C" & LetzteF & ";"""";0;1)"

       .Columns(2).NumberFormat = "@"

       .Columns(3).FormulaLocal = "=XVERWEIS(A2;Filme!B2:B" & LetzteF & ";Filme!E2:E" & LetzteF & ";"""";0;1)"

       .Columns(5).FormulaLocal = "=XVERWEIS(D2;Leute!B2:B" & LetzteL & ";Leute!D2:D" & LetzteL & ";"""";0;1)"

       .Columns(6).FormulaLocal = "=WENN(XVERWEIS(D2;Leute!B2:B" & LetzteL & ";Leute!C2:C " & LetzteL & ";"""";0;1)="""";"""";XVERWEIS(D2;Leute!B2:B" & LetzteL & ";Leute!C2:C " & LetzteL & ";"""";0;1)"

      .Formula = .Value2

End With

        

       With Worksheets("Liste").ListObjects(1).DataBodyRange

    .Sort Key1:=.Cells(1, 3), Order1:=xlDescending, Key2:=.Cells(1, 6), order2:=xlDescending, Header:=xlNo, Orientation:=xlTopToBottom

       End With

       

            Application.CutCopyMode = False

            Application.ScreenUpdating = True

     End Sub

Was habe ich nun jetzt wieder falsch gemacht?
14.09.2023 07:03:18
ralf_b
der Punkt vor Rows.count ist falsch, weil es keine With klammer drum rum gibt.
das du schon Lösungen hast ,die sich auf die Listobjects beziehen hier mal was für spalte 2 in Liste. Das wäre die Variante als Eintrag in die Zelle. VBA passt du dir dann an. 
=XVERWEIS([@Spalte1];Filme[Const]; Filme[Title];"";0;1)
Endergebnis
15.09.2023 10:32:28
Christian
Hallo,

ich melde mich jetzt erst wieder, weil ich die Mappe komplett neu strukturiert habe u.a. auch einige Blätter zusammengefasst habe.
Das ist jetzt dank eurer Hilfe mein funktionierendes Makro: (und ja das Sortieren mittendrin ist Absicht)

Ich wünsche euch ein schönes Wochenende und vielen Dank für die Unterstützung
Christian 

Sub Ergebnis()

        Dim LetzteF As Long

        Dim LetzteL As Long

        Application.ScreenUpdating = False

        

        LetzteF = Worksheets("Filme").Cells(Rows.Count, 1).End(xlUp).Row

        LetzteL = Worksheets("Leute").Cells(Rows.Count, 1).End(xlUp).Row

        

    With Worksheets("Ergebnis").ListObjects(1).DataBodyRange

             

       .Columns(2).NumberFormat = "General"

       .Columns(2).FormulaLocal = "=XVERWEIS(A2;Filme!B$2:B" & LetzteF & ";Filme!C$2:C" & LetzteF & ";"""";0;1)"

       .Columns(2).NumberFormat = "@"

       .Columns(3).FormulaLocal = "=XVERWEIS(A2;Filme!B$2:B" & LetzteF & ";Filme!E$2:E" & LetzteF & ";"""";0;1)"

       .Columns(5).FormulaLocal = "=XVERWEIS(D2;Leute!B$2:B" & LetzteL & ";Leute!C$2:C" & LetzteL & ";"""";0;1)"

       .Columns(6).FormulaLocal = "=XVERWEIS(D2;Leute!B$2:B" & LetzteL & ";Leute!D$2:D" & LetzteL & ";"""";0;1)"

       .Columns(7).FormulaLocal = "=DATEDIF(F2;C2;""Y"")"

       .Columns(8).FormulaLocal = "=DATEDIF(F2;C2;""YD"")"

       .Columns(9).FormulaLocal = "=C2-F2"

       .Formula = .Value2

       

            .Sort Key1:=.Cells(1, 3), Order1:=xlAscending, Key2:=.Cells(1, 6), order2:=xlDescending, Header:=xlYes, Orientation:=xlTopToBottom

            

       .Columns(10).FormulaLocal = "=WENN(ZÄHLENWENN(D$2:D2;D2)=1;F2;"""")"

       .Columns(11).FormulaLocal = "=RANG.GLEICH(F2;J$2:J2;0)"

       .Columns(12).FormulaLocal = "=MAX(F$2:F2)"

       .Columns(13).FormulaLocal = "=WENN(ANZAHL(J$2:J2)30;"""";KGRÖSSTE(J$2:J2;30))"

       .Columns(14).FormulaLocal = "=WENN(ODER(M2="""";J2="""";K2>30);"""";DATEDIF(M2;C2;""Y""))"

       .Columns(15).FormulaLocal = "=WENN(N2="""";"""";DATEDIF(M2;C2;""YD""))"

       .Columns(16).FormulaLocal = "=WENN(N2="""";"""";C2-M2)"

       .Columns(17).FormulaLocal = "=WENN(N2="""";"""";C2-M2)"

       .Columns(18).FormulaLocal = "=WENN(K2=30;TEXT(I2;""00000"")&"" ""&WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(B2;""/"";"""");"":"";"""");""*"";"""");"""""""";"""");""?"";"""")&"" (""&TEXT(C2;""TT.MM.JJJJ"")&"") - ""&E2&"" (""&TEXT(F2;""TT.MM.JJJJ"")&"") ""&G2&""-""&H2;"""")"

       .Formula = .Value2

    

    End With

                            

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

End Sub
es gilt weiterhin: bitte wer anders
12.09.2023 17:46:44
Oberschlumpf
ja, ich weiß, ich hab nix zum/über den Sortier-Code geäußert
aber ich hab doch den Rest-Code sehr stark verkürzt, damit optimiert
und ich bin ja nich der Einzige, der hier antworten könnte...warum sich niemand anderes beteiligt, kann ich dir nich verraten

nur noch n kleiner Tipp: der rote Text "Frage noch offen" neben Betreff mit CheckBox davor, stellt eine Option zur Verfügung, die genutzt werden sollte, wenn eine Frage nicht vollumfänglich beantwortet wurde; dies zeigt anderen möglichen Antwortern: "hier besteht noch Bedarf" - du nutzt diese Option nicht - wieso nich? ...na ja, du musst diese Frage nicht beantworten, ich hatte nur darauf hingewiesen
es gilt weiterhin: bitte wer anders
12.09.2023 18:35:18
Christian
Thorsten,

ich glaube wir reden im Moment sehr viel aneinander vorbei.
Selbstverständlich hast du das getan, ich wollte das auch nie in Frage stellen.

Alle Hinweise auf Probleme bezogen sich auf den von dir gesendeten Code nicht auf meinen. Da wir m.E. im Dunkeln getappt sind, wo das Problem mit der Formatierung liegt, hast du mich um eine Bsp. Datei gebeten, die wiederum m.e. nach erstmal hat darstellen müssen, was der funktionierende Code machen soll.

Ich kann dir natürlich auch gerne eine Datei mit dem Code der falsche Formatierungen macht schicken wenn dir das mehr hilft.

Gruß
Christian
es gilt weiterhin: bitte wer anders
12.09.2023 19:57:49
Oberschlumpf
danke, nein, ich brauch keine weitere Datei, weil...siehe Betreff
AW: bitte wer anders
12.09.2023 17:20:11
Oberschlumpf
ich hab genug probiert,empfohlen...ich mag nicht mehr
Makro optimieren.
09.09.2023 22:22:17
Christian
Hallo Thorsten, danke erstmal. Sorry war den ganzen Nachmittag/abend eingeladen, werde morgen in laufe des Tages ausführlich testen und dann Rückmeldung geben.

Christian
Makro optimieren.
10.09.2023 08:28:06
Christian
Hallo Thorsten,

sorry nochmal für die Verspätung aber wie gesagt ich war den ganzen Tag unterwegs.
so ich habe jetzt mal geschaut. Das Makro läuft ohne Fehlermeldung. Allerdings eine Sache hast du anders umgesetzt, als es im Original war.
Die Formatierungen betrafen in meinem Makro nur die Spalte B (2). Dein Makro hat in den Spalten C und F aus Kalenderdaten Zahlen gemacht, also auch diese Spalten formatiert.

Aber aus einer Sache werde ich nicht so ganz schlau, wenn ich jetzt irgendwann mal noch eine Formel hinzufügen möchte in Spalte G sagen wir einfach mal.

das heißt ich muss For lloCol = 2 To 6
in 7 ändern

einen Case 7 mit der Formel hinzufügen

und beim Sortieren bei .SetRange Range("A2:F" & loLetzte)
in G ändern.

Oder muss ich noch mehr tun?

Danke
Christian



AW: Makro optimieren.
10.09.2023 09:08:13
Oberschlumpf
Hi Christian,

stimmt, du hast recht. Nur für Spalte B soll das Zahlenformat auf "Standard" gesetzt werden. Das hatte ich übersehen.

Ändere diese eine Codezeile... 


.NumberFormat = "General"

...um in... 


If spalte = 2 Then

    .NumberFormat = "General"

End If

...so sollte das Format anderer Spalten "unberührt" bleiben.

Wenn allein diese Code-Änderung nicht hilft, dann zeig bitte eine Bsp-Datei, da ich ja sonst weiterhin nur raten, aber noch immer nicht wirklich testen kann.

Zitatauszug: "...wenn ich jetzt irgendwann mal noch eine Formel hinzufügen möchte in Spalte G sagen wir einfach mal. "

Ja, dann müsstest du den Code entsprechend so anpassen, wie von dir richtig vermutet.

Konnte ich helfen?

Ciao
Thorsten

Links zu Excel-Dialogen

CheckBox Frame

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige