Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1660to1664
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

Makro zu langsam

Makro zu langsam
08.12.2018 06:52:34
Peter
Guten Morgen ihr Excelspezialisten,
ich habe eine fast fertige Datei. Es besteht nur noch ein kleines Problem.
Wenn ich den Commandbutton6 drücke dauert die Bearbeitung relativ lange. Könnt ihr mir bitte helfen, die Arbeitszeit des Makros zu verkürzen.
Es werden die Werte von den beiden Tabellen 1 und 4 in SpalteS miteinander verglichen. Ich vermute, dass es dieser Teil des Makros ist, welcher lange dauert. Hier das Makro:
'gewählte E-Mail-Adressen auflisten und in Tabelle1 markieren
Private Sub CommandButton6_Click()
'Anfang Verkettung
Dim wb As Workbook                              'benötigt für aktuelle Workbook
Dim WS4 As Worksheet                            'benötigt für E-Mail Programm
Dim lz2 As String                                'benötigt für Combobox1 E-Mail Programm
Dim lz As String
Dim I As Long, strZusammen As String            'benötigt für Verkettung
Set wb = ThisWorkbook                           'benötigt für für aktuelle Workbook
Set WS4 = wb.Sheets("Tabelle4")             'benötigt für Combobox1 E-Mail Programm
With WS4
.Range("AF2").ClearContents
For I = 2 To .Cells(.Rows.Count, 19).End(xlUp).Row
If strZusammen = "" Then
strZusammen = .Cells(I, 19)
Else
strZusammen = strZusammen & "," & .Cells(I, 19)
End If
Next I
.Range("AF2") = strZusammen
End With
WS4.Range("AF2").EntireColumn.AutoFit
'Übertrag der gewählten E-Mails in Textbox1
Me.TextBox3 = Sheets("Tabelle4").Range("AF2").Value
'Ende Verkettung
'Anfang Wert suchen und x in Tabelle1 einfügen
Dim Zeile As Long
Dim varWert As Variant
Dim rngFound As Range
Dim wks_1 As Worksheet, wks_2 As Worksheet
Set wks_1 = ActiveWorkbook.Worksheets("Tabelle1")
Set wks_2 = ActiveWorkbook.Worksheets("Tabelle4")
With wks_1
'Anfang Autofilter ohne Meldung entfernen
On Error Resume Next
.ShowAllData
On Error GoTo 0
'Ende Autofilter ohne Meldung entfernen
For Zeile = 2 To .Cells(.Rows.Count, 19).End(xlUp).Row
If .Cells(Zeile, 19).Text  "" Then
varWert = .Cells(Zeile, 19).Value
Set rngFound = wks_2.Range("S:S").Find(what:=varWert, LookIn:=xlValues, lookat:= _
_
xlWhole)
If rngFound Is Nothing Then
.Cells(Zeile, 26).ClearContents
Else
.Cells(Zeile, 26).Value = "x"
End If
End If
Next
End With
'Ende Wert suchen und x in Tabelle1 einfügen
CommandButton6.Enabled = False
ListBox2.SetFocus
End Sub
Kann es sein, dass dieser Teil:
Set rngFound = wks_2.Range("S:S").Find(what:=varWert, LookIn:=xlValues, lookat:= _
xlWhole)
die Geschwindigkeit beeinflusst?
Ist es ggf. möglich und mit Zeitvorteil, wenn statt wks_2.Range(S2:S" & letzteZeile) einzufügen bzw. statt letzte Zeile z. B. S1000.
Besten Dank für eure Hilfe.
Gruss
Peter

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro zu langsam
08.12.2018 09:20:52
Matthias
Hallo Peter,
die Frage hast Du Dir doch schon selbst beantwortet.
Warum probierst Du es denn nicht einfach aus.
Ist ja logisch das man einen Spaltenbereich schneller durchsuchen kann als eine komplette Spalte.
Deinen Code habe ich nicht getestet, da ich das nicht nachbaue.
Gruß Matthias
AW: Makro zu langsam
08.12.2018 10:31:46
Peter
Hallo Matthias,
besten Dank - ich werde es testen, wollte mich nur bei einem Spezialisten informieren.
Wünsche noch ein schönes Wochenende.
Gruss
Peter
AW: Makro zu langsam
08.12.2018 09:47:58
Herbert
Hallo Peter,
probiers mal mit
Application.EnableEvents = False
.
.
.
Application.EnableEvents = True
Servus
Anzeige
AW: Makro zu langsam
08.12.2018 10:32:14
Peter
Hallo Herbert,
besten Dank - ich werde es testen, wollte mich nur bei einem Spezialisten informieren.
Wünsche noch ein schönes Wochenende.
Gruss
Peter
testen genügt doch-warum immer fragen?
08.12.2018 16:06:58
robert
AW: Makro zu langsam
08.12.2018 16:26:55
Peter
Hallo Herbert,
Dein Tipp hat den von mir erwünschten Erfolg gebracht.
Wünsche noch ein schönes Wochenenden.
Gruss
Peter
AW: Gerne geschehen! owt
08.12.2018 17:34:01
Herbert
,,
Unnötige Schleifen, schneller mit Formeln
08.12.2018 12:15:11
Daniel
Hi
wie die Kollegen schon schrieben: versuch macht kluch
sowas brauchst du nicht fragen, sondern kannst du einfach selber testen.
ich vermute mal, es bringt nicht viel, weil
1. wenn der suchbegriff vorhanden ist, bricht die Suche beim ersten Fund ab, dann ist es egal, wie weit du den Suchbereich definiert hast.
2. wenn der Suchbegriff nicht vorhanden ist und die Suche jeden Wert prüfen muss, dann durchsucht Excel idR auch nur den tatsächlich genutzten Zellbereich und nicht die komplette Spalte.
Dh den Bereich einzuschränken würde nur was bringen, wenn es Spalten gibt, die wesentlich weiter befüllt sind als die durchsuchte Spalte
das Hauptproblem ist, dass du jede Zelle einzeln bearbeitest, was viel Zeit braucht.
deutlich schneller wirds, wenn du statt der Schleife für jede Zelle eine entsprechende Formel verwendest, die du in alle Zellen gleichzeitig schreiben kannst:
Dann muss Excel nämlich die Aufgaben, die es beim Ändern eines Zellwertes ausführen muss, nicht für jede Zelle erneut ausführen, sondern kann das für alle Zellen gemeinsam tun.
Ersetze deine komplette For-Next-Schleife durch diesen Code:
with wks1.Range("Z2:Z" & wks1.Cells(rows.count, 19).end(xlup).Row)
.FormulaR1C1 = "=IF(RC19="""","""",IF(CountIf(Tabelle4!C19,RC19)=0,"""",""x""))"
.Formula = .Value
End with
sollte das immer noch zu langsam sein, solltest du folgendes tun:
Tabelle4 nach Spalte S aufsteigend sortieren um mit dem SVerweis mit 4. Parameter = WAHR prüfen zu können, ob der Wert vorhanden ist oder nicht.
In sortieren Listen kann nämlich der SVerweis die wesentlich schnellere Binäre Suche verwenden (lohnt sich vor allem bei sehr langen listen):
wks2.usedrange.Sort Key1:=wks2.Cells(1, 19), order1:=xlascending, header:=xlyes
with wks1.Range("Z2:Z" & wks1.Cells(rows.count, 19).end(xlup).Row)
.FormulaR1C1 = "=IF(RC19="""","""",IF(VLookUp(RC19,Tabelle4!C19,1,true)=RC19,""x"",""""))"
.Formula = .Value
End with

Gruß Daniel
Anzeige
AW: Unnötige Schleifen, schneller mit Formeln
08.12.2018 16:25:41
Peter
Hallo Daniel,
besten Dank für Deine Hilfe.
Der Tipp von Herbert hat bereits den Erfolg gebracht.
Wünsche noch ein schönes Wochenenden.
Gruss
Peter

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige