PDA

Vollständige Version anzeigen : vlookup durch Find + Copy ersetzen wie?


NickVBA
12.09.2011, 11:25
Hallo

Ich habe einen Code der in einer For-Schleife für jede Zeile in der Tabelle für 3 Spalten in einer externen Datei nachschaut ob es den entsprechenden Vergleichswert gibt und den korrespondierenden Spaltenwert dann in die jeweilige Spalte der Ausgangstabelle kopiert. Es werden also in meinem Bsp. immer 8900 Zeilen jeweils 3x (Spaltenweise) SWERWEIS / VLOOKUP Operationen durchgefürt. ---> idiotisch! Es dauert immer ca. 15min bis er durch ist. :boah:

Also meine Idee suche in der Verlgeichstabelle meinen Vergleichswert und kopieren dann die entsprechenden 3 Werte in die Ausgangstabelle um. Nur wie mache ich das in VBA Code? Bitte um Hilfe! Meine Versuche mit Find haben nix brauchbares gebracht weil ich irgendwie nicht den Dateipfad rein gebracht habe.

Hier mal ein Auszug aus meinem Code:

...

sPath = ThisWorkbook.Path & "\Import\" 'Pfad mit Unterverzeichnis für Vergleichstabelle
sFile = Range("I1") & "_" & Range("H1") & "_Import.xlsx" 'Dateiname aus Angaben in der Ausgangstabelle
sWks = "Worksheets(1)" 'Tabelle wo Daten stehen
sRng = "R2C4:R2400C9" 'Bereich für vlookup, G2:J2400 - nicht dynamisch


For i = 3 To lZ 'lz ist Tabellenende, i Zählervariable
'Daten holen über Prüfung mit Import Datei
formel1 = "VLOOKUP(RC[-5],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",5,FALSE)" 'Anzahl
ActiveSheet.Cells(i, 11).FormulaR1C1 = "=if(iserror(" & formel1 & "),0," & formel1 & ")" 'prüfung Wert da

formel1 = "VLOOKUP(RC[-7],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",3,FALSE)" 'Abrechnung
ActiveSheet.Cells(i, 13).FormulaR1C1 = "=if(iserror(" & formel1 & "),0," & formel1 & ")" 'prüfung Wert da

formel1 = "VLOOKUP(RC[-8],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",4,FALSE)" 'Umsatz
ActiveSheet.Cells(i, 14).FormulaR1C1 = "=if(iserror(" & formel1 & "),0," & formel1 & ")" 'prüfung Wert da
Next i

...

EarlFred
12.09.2011, 11:38
Hallo,

ist es Bedingung, dass die Datei, aus der Du die Daten holst, geschlossen bleibt? Hast Du mal probiert, ob's mit einer geöffneten Datei schneller geht?
Und FIND auf geschlossene Dateien anwenden? Ich weiß ja nicht...

Das sollten wir erstmal klären, dann finden wir (vielleicht) auch eine geeignete Methode.

Grüße
EarlFred

NickVBA
12.09.2011, 11:50
Hallo

Ja ich setze es mal als Bedingung das meine Vergleistabelle geschlossen bleibt. Sicher kann ich es hier noch abändern aber ich will die "Anwedung" für andere Benutzer so "idiotensicher" wie möglich halten.

Kann man denn nicht mit einem Trick nur die 2x zuviel ausgefürten Vlookup Aufrufe pro Zeile kürzen - es reicht doch wenn der Vergleichswert einmal gefunden wurde?

Wenn es eine andere Möglichkeit zu FIND gibt, ich bin für jeden Gedanken dankbar.

NickVBA
13.09.2011, 08:15
Hallo

Ok es war nicht unbedingt meine Frage aber nach einer schlaflosen Nacht bin ich auf die Idee gekommen die FOR-Schleife weg zu lassen und das ganze mit WITH und RANGE zu versuchen.
Ergebnis: Durchlauf vorher ca. 18 Minuten, Durchlauf jetzt ca. 4 Sekunden (jeweils 8874 Zeilen)?! :entsetzt:

...


With ActiveSheet.Range("K3:K" & lZ) 'Spalte K
formel1 = "VLOOKUP(RC[-5],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",5,FALSE)" 'Geräte im MP
.FormulaR1C1 = "=if(iserror(" & formel1 & "),"" ""," & formel1 & ")" 'prüfung Wert da sonst
.Value = .Value
End With

With ActiveSheet.Range("L3:L" & lZ) 'Spalte L
formel1 = "VLOOKUP(RC[-6],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",2,FALSE)" 'Miettage
.FormulaR1C1 = "=if(iserror(" & formel1 & "),"" ""," & formel1 & ")" 'prüfung Wert da
.Value = .Value
End With

With ActiveSheet.Range("M3:M" & lZ) 'Spalte M
formel1 = "VLOOKUP(RC[-7],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",3,FALSE)" 'Abgerechnete Tage
.FormulaR1C1 = "=if(iserror(" & formel1 & "),"" ""," & formel1 & ")" 'prüfung Wert da
.Value = .Value
End With

With ActiveSheet.Range("N3:N" & lZ) 'Spalte N
formel1 = "VLOOKUP(RC[-8],'" & sPath & "[" & sFile & "]" & sWks & "'!" & sRng & ",4,FALSE)" 'Umsatz
.FormulaR1C1 = "=if(iserror(" & formel1 & "),"" ""," & formel1 & ")" 'prüfung Wert da
.NumberFormat = "#0.00"
.Value = .Value
End With

...