Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1920to1924
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
Inhaltsverzeichnis

Makroausführung braucht zu lange

Makroausführung braucht zu lange
02.03.2023 13:35:03
Hens135
Hallo zusammen,
ich benötige eure Hilfe. Und zwar habe ich vor einigen Wochen hier ein super Makro erhalten, was meine Arbeitstabelle um Daten einer neuen Tabelle ergänzt und anhand eines Kriteriums die restlichen Spalten innerhalb der Zelle richtig wiedergibt.
Ich habe das Makro jetzt in meine Praxis umgesetzt, da wir hier aber von einigermaßen vielen Datensätzen sprechen (6000 Zeilen und 40 Spalten) benötigt das Makro etwa 3 31/2 Stunden bis es durchgelaufen ist.
Daher meine Fragen, ob jemand von euch Möglichkeit der Verschlankung des Makros sieht?
Ich habe dazu auch eine Beispieldatei hochgeladen. Allerdings ist die tatsächliche Datei deutlich größer.
https://www.herber.de/bbs/user/158093.xlsm

Sub Daten_uebertragen()
 Debug.Print Now
 
 Dim i As Long, Zeile As Long, letzteZeile As Long
 Dim Arbeitsmappe As Workbook
 Dim Datenbasis As Worksheet, Ziel As Worksheet
 Dim ZelleKD_NR As Range, Bereich As Range
 Dim iName As Integer, iTel As Integer, iNr As Integer, iOrt As Integer, _
   iStrasse As Integer, iPLZ As Integer, iBranche As Integer, iKdNr As Integer
 
 Set Arbeitsmappe = ThisWorkbook
 Set Datenbasis = Arbeitsmappe.Worksheets("1. Update aus SAP")
 Set Ziel = Arbeitsmappe.Worksheets("Bestandstabelle")
 
 With Datenbasis
   For i = 1 To 8
     Select Case .Cells(1, i)
       Case "Branchen_Art": iBranche = i
       Case "Firma_Name": iName = i
       Case "KD_NR": iKdNr = i
       Case "PLZ": iPLZ = i
       Case "Ort": iOrt = i
       Case "PHONE_NR": iTel = i
       Case "Straße": iStrasse = i
       Case "NR": iNr = i
     End Select
   Next i
 End With
 
 
 With Datenbasis
   letzteZeile = .Cells(Rows.Count, iKdNr).End(xlUp).Row
   Set Bereich = .Range(.Cells(2, iKdNr), .Cells(letzteZeile, iKdNr))
 End With
 
 For i = 2 To Ziel.Range("C" & Rows.Count).End(xlUp).Row
     With Datenbasis
         Set ZelleKD_NR = Bereich.Find(Ziel.Range("C" & i).Value, LookIn:=xlValues, lookat:=xlWhole)
         If Not ZelleKD_NR Is Nothing Then
             Ziel.Range("A" & i).Value = .Cells(ZelleKD_NR.Row, iBranche).Value
             Ziel.Range("B" & i).Value = .Cells(ZelleKD_NR.Row, iName).Value
             Ziel.Range("D" & i).Value = .Cells(ZelleKD_NR.Row, iPLZ).Value
             Ziel.Range("E" & i).Value = .Cells(ZelleKD_NR.Row, iOrt).Value
             Ziel.Range("F" & i).Value = .Cells(ZelleKD_NR.Row, iStrasse).Value
             Ziel.Range("G" & i).Value = .Cells(ZelleKD_NR.Row, iNr).Value
             Ziel.Range("H" & i).Value = .Cells(ZelleKD_NR.Row, iTel).Value
             Set ZelleKD_NR = Nothing
         End If
     End With
 Next i
 
 Debug.Print Now
 End Sub

Danke vorab und liebe Grüße
Henrik

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makroausführung braucht zu lange
02.03.2023 15:38:33
onur
Eigentlich seher ich da nix, was irgendwas um irgendwas ergänzt. Eigentlich wird in deiner Beispelsdatei nur alles nach Kundennummer sortiert.
AW: Makroausführung braucht zu lange
02.03.2023 15:55:20
onur
Und das kannst du auch mit einer einzigen Formel erreichen.
AW: Makroausführung braucht zu lange
02.03.2023 16:01:59
Daniel
Hi
probier mal das:
Sub Daten_uebertragen_schnell()
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet, Ziel As Worksheet
Dim ZelleD As Range
Dim ZelleZ As Range
Dim DB_relevant As Range
Dim Ziel_relevant As Range
Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("1. Update aus SAP")
Set Ziel = Arbeitsmappe.Worksheets("Bestandstabelle")
Datenbasis.UsedRange.Sort Key1:=Datenbasis.Cells(1, 3), order1:=xlAscending, Header:=xlYes
Ziel.UsedRange.Sort Key1:=Ziel.Cells(1, 3), order1:=xlAscending, Header:=xlYes
With Datenbasis.UsedRange
    With .Columns(.Columns.Count + 1)
        .FormulaR1C1 = "=if(vlookup(rc3,'" & Ziel.Name & "'!C3,1,1)=rc3,1,"""")"
        .Formula = .Value
        .Cells(1, 1).Value = "a"
    End With
End With
With Ziel.UsedRange
    With .Columns(.Columns.Count + 1)
        .FormulaR1C1 = "=if(vlookup(rc3,'" & Datenbasis.Name & "'!C3,1,1)=rc3,1,"""")"
        .Formula = .Value
        .Cells(1, 1).Value = "b"
    End With
End With
With Datenbasis.UsedRange
    .Sort Key1:=.Cells(1, .Columns.Count), order1:=xlAscending, Header:=xlYes
    Set DB_relevant = .Columns(.Columns.Count).SpecialCells(xlCellTypeConstants, 1)
End With
With Ziel.UsedRange
    .Sort Key1:=.Cells(1, .Columns.Count), order1:=xlAscending, Header:=xlYes
    Set Ziel_relevant = .Columns(.Columns.Count).SpecialCells(xlCellTypeConstants, 1)
End With
For Each ZelleD In Datenbasis.UsedRange.Rows(1).Cells
    Set ZelleZ = Ziel.Rows(1).Find(what:=ZelleD.Value, lookat:=xlWhole)
    If Not ZelleZ Is Nothing Then
        Intersect(ZelleD.EntireColumn, DB_relevant.EntireRow).Copy
        ZelleZ.Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next
With Ziel.UsedRange
    .Sort Key1:=.Cells(1, 3), order1:=xlAscending, Header:=xlYes
    .Columns(.Columns.Count).ClearContents
End With
        
With Datenbasis.UsedRange
    .Sort Key1:=.Cells(1, 3), order1:=xlAscending, Header:=xlYes
    .Columns(.Columns.Count).ClearContents
End With
End Sub
der Code ermittelt zunächst, welche Kunden in beiden Tabellen vorhanden sind und sortiert beide Listen dann so, dass diese Kunden direkt untereinander in der gleichen Reihenfolge stehen.
Dann werden die Daten für die die Spalten, die in beiden Listen vorhanden sind, aus dem Update in die Bestandstabelle kopiert
Gruß Daniel
Anzeige
AW: Makroausführung braucht zu lange
02.03.2023 17:26:30
Hens135
Hallo Daniel,
danke dir sehr für deine Hilfe. Ich glaube, dass mein vorheriges Makro ein wenig zu verkopft war.
Ich habe ein gutes Gefühl, dass dein Makro für mich gut funktionieren könnte.
Allerdings habe ich eine erste Herausforderung ausgemacht. Und zwar sind in der Bestandstabelle die Spaltenüberschriften 3-zeilig, sodass die erste beschriebene Zeile erst die 4 ist. Hatte den Code versucht anzupassen, aber ohne erhofften Ergebnis. Der sortiert dennoch immer direkt ab der 2. Zeile.
Ziel.UsedRange.Sort Key1:=Ziel.Cells(4, 3), order1:=xlAscending, Header:=xlYes
Danke dir vorab
Anzeige
AW: Makroausführung braucht zu lange
02.03.2023 18:09:48
Daniel
Hi
ich habe mich an deine Beispieldatei gehalten.
da gibt es nur eine Zeile überschrift.
welche Zeile du beim Key angibst, ist egal, hier kommt es nur auf die Spalte an, nach der sortiert werden soll.
mit Header:=xlYes wird immer nur von einer einzeiligen Überschrift ausgegangen.
dh du kannst, wenn du mehrere Zeilen Überschrift hast, nicht mehr so einfach mit Usedrange arbeiten , sondern musst den Zellbereich etwas aufwendiger angeben, z.B. Range(Cells(3, 1), Cells.SpecialCells(xlcelltypeLastcell))
die nächste Frage wäre dann noch die Überschriften übereinstimmen. Wenn nicht, dann müsstest du halt beim Kopieren jede Spalte einzeln mit expliziter Angabe der Quell- und Zielspalte kopieren.
wie gesagt, ich kann mich nur nach dem richten, was du mir zeigst und dieses nachschieben von Anforderungen ist extrem lästig.
im "richtigen Leben" wird das auch schnell teuer, wenn der Programmierer da nochmal ran muss, weil du die Aufgabe falsch beschrieben hast.
Gruß Daniel
Anzeige
AW: Makroausführung braucht zu lange
02.03.2023 17:43:06
Hens135
Hat sich erledigt. Habe das Makro weiter getestet und wird für mich nicht funktionieren. Um das zu erklären muss ich meine gesamte Anforderungen einmal schildern.
Ich arbeite mit einer Kundenübersicht (Bestandstabelle) bei der ein großer Teil der Informationen aus dem System kommen. Zusätzlich arbeite ich aber noch mit Anmerkungen zu den Kunden, die nicht im System enthalten sind und somit nur in meinem Arbeitsblatt enthalten sind (Bestandstabelle).
Neue Kunden füge ich der Tabelle nicht direkt hinzu und Änderungen an Kundendaten wie beispielsweise Kündigungsdatum (um bei der Beispieldatei zu bleiben: Telefonnummer) trage ich auch nicht in die Excel-Tabelle ein. Diese Änderungen und neuen Kunden ziehe ich mir aus dem System in Form einer Gesamtkundenübersicht (in dieser Liste werden alle bestehenden Kunden aufgelistet).
Nun möchte ich meine bestehende Kundendatei um die neuen Datensätze ergänzen und Änderungen der bestehenden Kunden übernehmen.
Daher stammt auch der erste Makroausschnitt, den ich mit angefügt hatte. Meine Vorgehensweise war wie folgt:
  • Alle Kundennummern aus neuer Datei in Spalte "KD_NR" in Bestandstabelle eintragen.

  • Spalte KD_NR auswählen und Duplikate entfernen

  • restlichen Spalte zur passenden Kundennummer befüllen (wichtig: Aktualisierungen bestehender Kunden müssen berücksichtigt werden)

  • Tut mir leid, dass ich mich eingangs nur schwammig ausgedrückt hatte. Jedoch war ich der Annahme, dass ich mit dem "Projekt" schon weiter bin und einfach das bestehende Makro optimieren kann. Allerdings scheine ich das Makro weniger zu verstehen, als ich geglaubt habe.
    Also wenn jemand eine Lösung für mich hat, würde ich mich freuen.
    Liebe Grüße
    Henrik
    Anzeige
    AW: Makroausführung braucht zu lange
    03.03.2023 10:55:47
    Herbert_Grom
    Hallo Henrik,
    bei mir dauert die Ausführung deines Makros in der Beispieldatei 1 Sekunde (s. Bild)!
    Userbild
    Um mehr sagen zu können, bräuchte ich die Original-Datei mit den 6000 Zeilen und 40 Spalten. Die kannst du ja mal zippen und mir per eMail schicken. Meine Adr findest du hier in den Profilen. Datenschutz ist garantiert!
    Servus
    AW: Makroausführung braucht zu lange
    02.03.2023 17:43:57
    Hens135
    s

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige