Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
972to976
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
972to976
972to976
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

VLookup mit VBA

VLookup mit VBA
07.05.2008 14:24:01
Bertram
Hallo Leute,
meine Beispieldatei ist ein Auszug aus der Originaldatei. Im Original hat Tabellenblatt "Materialien" rund 6.000 Einträge und "Orders" rund 45.000. Desweitern gibt es noch 8 weitere Tabellenblätter, die nur Daten enthalten, also keine Formeln, die neu berechnet werden.
Mit dem Code "MaterialEintragenInOrders" lasse ich in Tabellenblatt "Orders" die Materialnamen eintragen.
Das funktioniert so auch ganz gut, allerdings dauert das in der Originaldatei ca. 40 Sekunden. Das macht im Grunde nichts, da die zugehörigen Materialien nur einmalig alle 3 Monate eingetragen werden. Zuerst hatte ich die SVerweise als Formel, aber es ist ja nicht nötig diese jedes mal neu zu berechnen.
Habe ich den Code nur unnötig kompliziert geschrieben, dass das so lange dauert, oder ist das bei der Datenmenge normal? Dünkt mich arg lang zu dauern.
Falls mein Vorhaben auch einfacher/schneller zu realisieren ist, bin ich für Tips dankbar.
Anmerkung: Im Besipiel ist if strmaterial "" nie TRUE, kann im Original aber sehr wohl vorkommen.
https://www.herber.de/bbs/user/52195.xls
Gruß
Bertram

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VLookup mit VBA
07.05.2008 14:46:00
Bertram
Hallo nochmal,
habe gerade gesehen, dass ich den falschen Code eingefügt habe. if strmaterial "" ist nicht richtig.
Hier der richtige Code:

Sub MaterialEintragenInOrders()
Dim rngMaterials As Range
Dim strMaterial As String
Dim i As Long
With Sheets("Materialien")
Set rngMaterials = .Range(.Cells(2, 1), .Cells(.Cells(1, 1).End(xlDown).Row, 2))
End With
With Sheets("Orders")
For i = .Cells(1, 3).End(xlDown).Row To 2 Step -1
On Error Resume Next
strMaterial = Application. _
WorksheetFunction.VLookup(.Cells(i, 3).Value, rngMaterials, 2, 0)
If Err.Number = 1004 Then
.Cells(i, 3).EntireRow.Delete
Err.Clear
On Error GoTo 0
Else
.Cells(i, 4).Value = strMaterial
End If
Next i
End With
End Sub


Gruß
Bertram

Anzeige
AW: VLookup mit VBA
07.05.2008 14:54:00
Renee
Hi Bertram,
Versuch es mal mit dem Turbo:

Sub MaterialEintragenInOrders()
Dim rngMaterials As Range
Dim strMaterial As String
Dim i As Long
GetMoreSpeed True
With Sheets("Materialien")
Set rngMaterials = .Range(.Cells(2, 1), .Cells(.Cells(1, 1).End(xlDown).Row, 2))
End With
With Sheets("Orders")
For i = .Cells(1, 3).End(xlDown).Row To 2 Step -1
On Error Resume Next
strMaterial = Application. _
WorksheetFunction.VLookup(.Cells(i, 3).Value, rngMaterials, 2, 0)
If Err.Number = 1004 Then
.Cells(i, 3).EntireRow.Delete
Err.Clear
On Error GoTo 0
Else
.Cells(i, 4).Value = strMaterial
End If
Next i
End With
GetMoreSpeed False
End Sub
Sub GetMoreSpeed(bYesNo As Boolean)
Application.ScreenUpdating = Not (bYesNo)
Application.EnableEvents = Not (bYesNo)
Application.Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
If Not bYesNo Then Calculate
End Sub


Anzeige
AW: VLookup mit VBA
07.05.2008 15:08:41
Bertram
Hallo Renee,
das reduziert die Laufzeit schon mal auf ca. 4 Sekunden. Das ist ok so.
Danke dir.
Gruß
Bertram

AW: VLookup mit VBA
07.05.2008 15:19:57
Renee
Hi Bertram,
Wenn die Datenverteilung in deinem Beispiel ähnlich ist, wie in deinem produktiven Sheet, dürfte dieser Code noch einen Tick schneller sein:

Sub MaterialEintragenInOrders()
Dim rngMaterials As Range
Dim strMaterial As String
Dim i As Long
GetMoreSpeed True
With Sheets("Materialien")
Set rngMaterials = .Range(.Cells(2, 1), .Cells(.Cells(1, 1).End(xlDown).Row, 2))
End With
With Sheets("Orders")
For i = .Cells(1, 3).End(xlDown).Row To 2 Step -1
If .Cells(i, 3).Value = .Cells(i + 1, 3).Value Then
.Cells(i, 4).Value = strMaterial
Else
On Error Resume Next
strMaterial = Application. _
WorksheetFunction.VLookup(.Cells(i, 3).Value, rngMaterials, 2, 0)
If Err.Number = 1004 Then
.Cells(i, 3).EntireRow.Delete
Err.Clear
On Error GoTo 0
Else
.Cells(i, 4).Value = strMaterial
End If
End If
Next i
End With
GetMoreSpeed False
End Sub
Sub GetMoreSpeed(bYesNo As Boolean)
Application.ScreenUpdating = Not (bYesNo)
Application.EnableEvents = Not (bYesNo)
Application.Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
If Not bYesNo Then Calculate
End Sub


GreetZ Renée

Anzeige
Nachfrage
07.05.2008 15:19:00
Bertram
Hallo Renee,
habe gerade etwas rumprobiert und festgestellt, dass die Geschwindigkeit nur durch die automatische Berechnung gebremst wird (Das ScreenUpdating FALSE/TRUE hab ich normal schon drin *peinlicherweise rot werd*).
Kannst du mir sagen, wieso eine Neuberechnung bremst, wenn's nix zum neu berechnen gibt?
Gruß
Bertram

AW: Nachfrage
07.05.2008 15:25:00
Renee
Hi Bertram,
Falls sich irgendwelche Formeln in deinem Blatt auf Spalte D beziehen, wird jedesmal eine Neuberechnung gemacht. Auch bennante Bereiche oder Formeln in Namen, oder flüchtige Funktionen (Heute()) können zu diesem Effekt führen. Ist aber schwierig zu sagen, da ich deine Mappe nicht kenne.
GreetZ Renée

Anzeige
AW: Nachfrage
07.05.2008 15:32:07
Bertram
Hallo Renée,
dass wir nicht in zwei Zweigen weiterschreiben:-), erst mal die Antwort zu deinem ergänzten Code: Habe es im Original ausprobiert, dauert aber gefühlt etwas länger als nur mit Turbo.
Was die Neuberechnung angeht, stimme ich dir vollkommen zu, aber in der Datei gibt es keine einzige Formel, die berechnet werden müßte (nachdem ich sämtliche SVerweise ja vorher gelöscht hab). Kann das Original leider nicht hochladen. Ist erstens vertraulich und zweitens 8 MB groß.
Mit den 4-5 Sekunden kann ich leben. Widmen wir uns bei dem schönene Wetter lieber anderen Dingen:-)
Danke und Gruß
Bertram

Anzeige
AW: VLookup mit SPEEEEEED
07.05.2008 23:06:00
Daniel
Hi
ein Geschwindigkeitsproblem ist der SVERWEIS mit dem 4. Parameter 0, da hierbei jeder einzelne Zellwert des Suchvektors verglichen werden muss (dh, im Statistischen Mittel pro SVerweis bei 1000 Einträgen 500 vergleiche).
da deine Material-Liste offensichtlich sortiert ist, kannst du auch beim SVerweis den 4. Parameter 1 verwenden.
aufgrund der Sortierung kann ein schneller Suchalgorythmus verwendet werden (bei 1000 Einträgen c.a. 12 Vergleiche).
zu beachten ist lediglich, daß es bei fehlenden Einträgen KEINEN #NV-Fehler mehr als ergebnis gibt, sondern der davorliegende Wert zurückgegeben wird.
hier wäre der Workaround, daß man eine Wenn-Abfrage vorschaltet, mit nem SVererweis auf die Spalte 1 und das Ergebnis mit dem Suchwert vergleicht.
Die Formel ist zwar länger, aber immer noch um Welten schneller als der SVerweis mit 4. Parameter = 0
Lange Rede, Lange Formel, hier wäre die Formel für deine Beispieldatei:

=WENN(SVERWEIS(C2;Materialien!A:B;1;1)C2;"fehlt";SVERWEIS(C2;Materialien!A:B;2;1))


außderdem, wenn für ein Problem eine Formellösung besteht, aber ein Makro verwendet werden soll, dann ist es oft am einfachsten, per Makro nur die Formel in die Betroffenen Zellen zu schreiben und dann die Formel durch werte zu ersetzen.


Sub SverweisFormel()
With Sheets("Orders")
With Range(.Cells(2, "D"), .Cells(Rows.Count, "C").End(xlUp).Offset(0, 1))
.FormulaR1C1 = "=IF(VLOOKUP(RC[-1],Materialien!C[-3]:C[-2],1,1)RC[-1],TRUE,VLOOKUP(RC[ _
-1],Materialien!C[-3]:C[-2],2,1))"
.Formula = .Value
.SpecialCells(xlcelltypeValues, 4).EntireRow.Delete 'nicht gefundene Material-Nummern lö _
schen
End With
End With
End Sub


nicht gefundene Material-Nummern werden zunächst mit einem Wahrheitswert gekennzeichnet und dann die ganze Zeile gelöscht (ich hoffe, daß ist deine Absicht).
bei grossen Datenmengen in der Orderdatei würde ein vorheriges Sortieren nach der Materialbenennung auch einen Geschwindigkeitsvorteil bringen.
Gruß, Daniel
ps: Beachte: die Material-Liste MUSS aufsteigend nach der Material-Nr. sortiert sein.

Anzeige
AW: VLookup mit SPEEEEEED
08.05.2008 10:06:05
Bertram
Hallo Daniel,
danke für deine Tipps, aber erstaunlicherweise dauert die Berechnung deiner Formel bei mir ewig (vor allem, wenn ich komplette Spalten angebe).
Was das Makro angeht, das ist auch recht langsam. Was soll mir denn die Letzte Zeile (.SpecialCells...) sagen? Die Konstante xlCellTypeValues gibst bei mir nicht. Die 4 steht wahlscheinlich für xlErrors?
Gruß
Bertram

AW: VLookup mit SPEEEEEED
08.05.2008 15:20:40
Reinhard
Hi Bertram,
gemeint ist wahrscheinlich beim Type: xlCellTypeFormulas und die 4 steht für Xllogicals. Die 16 wäre XLErrors.
Wenn irgednio in der Hilfe steht die Werte können addiert werden, so handelt es sich meist um 1,2,4,8,16,...
Die Reihenfolge in der Auflistung
"Kann eine der folgenden XlSpecialCellsValues-Konstanten sein: xlErrors, xlLogical, xlNumbers oder xlTextValues."
spiegelt nicht die Wertigkeit wieder.
Gruß
Reinhard

Anzeige
Danke Reinhard oT
08.05.2008 15:39:00
Bertram

AW: VLookup mit SPEEEEEED
09.05.2008 00:35:00
Daniel
Hi
der Geschindigkeitsvorteil bezieht sich natürlich nur auf die beiden Varianten des SVeweises.
wenn du noch weitere Formeln in deiner Datei hast, die ebenfalls eine lange Rechenzeit verursachen, (SummeWenn, ZählenWenn und Matrix-Formeln sind da so Kandiaten), dann hilft auch bei meiner Vorgehensweise nur, die automatische Neuberechnung vorrübergehend zu deaktivieren.
zur zweiten Frage, das 4 steht für Wahrheitswerte (1: Zahlen, 2: Texte, 4: Wahrheitswerte, 16: Fehler, Kominationen durch Summenbildung).
der von mir verwendete SVerweis produziert ja keinen Fehler mehr, daher schreibe ich bei der ersten Prüfung bei nicht vorhanden Werten einen Wahrheitswert als Ergebnis zurück, denn ich dann mit der SpecialCells-Funktion geziehlt selektieren und löschen kann.
Gruß, Daniel
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige