Makro über Sverweis dauert ewig

Hallo zusammen,
ich habe folgenden Code erstellt:

Sub Makro1()
'Tabelle1
Application.ScreenUpdating = False
Sheets("Auswertung BW-Version Bestand").Activate
Columns("G:H").Insert Shift:=xlToRight
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Verkettung"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "SVerweis"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("G2").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
Range("G2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
'Tabelle2
Sheets("Hilfsblatt aus ETS addon").Activate
Columns("H:H").Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "Verkettung"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("H2").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-1])"
Range("H2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
'Tabelle1
Sheets("Auswertung BW-Version Bestand").Activate
Range("H2").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-1],'Hilfsblatt aus ETS addon'!C,1,0)"
Range("H2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Application.ScreenUpdating = True
End Sub

Dabei wird als letzter Schritt ein Sverweiß angewendet.
Problem 1: ich habe ungefähr 600.000 Zeilen
mit autofill xl.down wird der sverweis bis zur letzten Zeile gerechnet (Zeile 1048576). Das ist allerdings zuviel. Das Autofill xl.down sollte sich an die Zeilenanzahl wie z.B. in Spalte A steht ausrichten.
Problem 2: die Dauer
ich war immer der Ansicht, sobald ich ein Makro verwende rechnet er die 600.000 Zeilen schnell durch. Doch auch wenn ich den Sverweiß über Makro mache, warte ich ewig.
Kann jmd. bei den Problemen behilflich sein?
VG Berndt

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Makro1() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual With Sheets("Auswertung BW-Version Bestand") .Columns("G:H").Insert Shift:=xlToRight With .Columns("G:H").Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With .Range("G1") = "Verkettung" With .Range("G1:H1").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With With .Range("G2:G" & .Cells(Rows.Count, 1).End(xlUp).Row) .NumberFormat = "General" .FormulaR1C1 = "=+CONCATENATE(RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])" End With With .Range("H2:H" & .Cells(Rows.Count, 1).End(xlUp).Row) .NumberFormat = "General" .FormulaR1C1 = "=+VLOOKUP(RC[-1],'Hilfsblatt aus ETS addon'!C,1,0)" End With End With With Sheets("Hilfsblatt aus ETS addon") .Columns("H:H").Insert Shift:=xlToRight .Range("H1") = "Verkettung" With .Range("H1").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With With .Range("H2:H" & .Cells(Rows.Count, 1).End(xlUp).Row) .NumberFormat = "General" .FormulaR1C1 = "=+CONCATENATE(RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-1])" End With End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

Hi Chris,
gern geschehen: ich weiß immer ganz gerne, wie sich was verhält, und mit ein paar Testdaten wird's plausibel.
Die Frage ist halt, was tatsächlich benötigt wird...
Die concat wird ja (wahrscheinlich) nur vorgenommen, um sie in beiden Blättern im SVerweis verwenden zu können. Mit reinem VBA (also Arrays bzw. Dictionary) sind diese beiden Hilfsspalten (wenn sie denn wirklich *nur* dafür da sind) unnötig und man kann eigentlich jede Art von weiterzuverarbeitendem Ergebnis ausgeben (z.B. anstelle meiner 1 die Zeilennummer, falls man die benötigt).
Eine andere Geschichte ist das von Berndt und Dir verwendete Einfügen der Spalten G bzw. G+H, das ich stillschweigend unterschlagen habe (damit meine Buttons nicht nach rechts rutschen): das sorgt aber im Original dazu, daß pro Aufruf 600.000 mal 2 Spalten und x-1000 mal 1 Spalte, die FORMELN enthalten, zusätzlich erzeugt werden: das bremst denn auch.
Ich weiß schon: dictionary ist mit "VBA Basiskenntnissen" zunächst etwas schwer verdaulich...
Wenn Berndt fragen würde, könnten wir es ihm aber erklären.
Schöne Grüße,
Michael
P.S.: Beim dictionary bin ich mir im Moment selbst nicht schlüssig, wie man es einsetzen sollte. Die Feinheit liegt in den Zeilen

b(i, 2) = o(s) ' a)
If o.exists(s) Then b(i, 2) = o(s) ' b)

a) erzeugt massenweise "leere" weitere Einträge im Dictionary, sofern der Begriff nicht vorhanden ist. Deshalb habe ich die Anzahl der Einträge mit k2=o.count direkt nach der ersten Schleife abgegriffen.
Das Verwunderliche ist, daß diese Variante trotzdem schneller ist als b)
b) verändert das Dictionary nicht, die o.exists-Abfrage (oder bereits das reine If?) benötigt aber rund 10% mehr Zeit.
Wir wissen weder, wie viele Zeilen das Hilfsblatt besitzt, noch wie das Verhältnis von Gefundenen und nicht Gefundenen in der Auswertung ist.
Bei a) hat das Dictionary auf alle Fälle alle Einträge des Hilfsblatts (ohne Doppelte, aber SVerweis gibt ja auch nur jeweils "einen" Eintrag zurück - Dict. den letzten, SVerweis den ersten) plus alle nicht Gefundenen.

Beliebteste Forumthreads (12 Monate)

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

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread