VLOOKUP über VBA

Hallo,
ich möchte per VBA eine Zelle per SVERWEIS befüllen.
dim varSverweis as Variant
'die per Sverweis zu befüllende Zelle auswählen
Worksheets(strZielTabellenname).Range(Cells(2, lngZielSpaltennummerMitSuchkriterium + 1), Cells(2, lngZielSpaltennummerMitSuchkriterium + 1)).Select
Folgender weiterer Code funktioniert wunderbar:

With ActiveCell
varSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, Worksheets(strQuelltabelleAusDerUebersetztWird).Range("E:G"), 3, False)
End With
Will ich die Range ("E:G") und den Spaltenindex 3 nun durch Variablen ersetzen kriege ich Fehler 1004.
Hier der nicht funktionierende Code mit Variablen:
varSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, Worksheets(strQuelltabelleAusDerUebersetztWird).Range(Columns(lngQuellsuchspalte), Columns(lngQuellergebnisspalte)), lngSpaltenindexSverweis, False)
wobei hier...
lngQuellsuchspalte=5
lngQuellergebnisspalte=7
lngSpaltenindexSverweis=3
...ist
Danach führe ich den SVerweis dann so aus. Bei Variante 1 funktioniert dies auch problemlos. Bei Variante 2 komme ich erst garnicht soweit aufgrund des 1004er Fehlers:
ActiveCell.FormulaR1C1 = varSverweis
Hoffe mir kann jemand weiterhelfen... Sitze hier jetzt schon 2 3 Stunden dran und komme nicht dahinter was das Problem ist. Vielleicht gibt es ja andere Vorschläge wie ich Range ("E:G") und den Spaltenindex 3 hier über Variablen abgebildet bekomme.
Vielen Dank im Voraus!
GRuß Martin

Hi
da gibt's mehrere Möglichkeiten.
a) mit Worksheetfunction.Countif überprüfen, ob der gesuchte Wert in der Liste vorhanden ist (ist aber zeitauwendig, da du die Liste dann 2x durchsuchst)
b) statt Application.Worksheetfunction.VLookUp nur Application.VLookUp verwenden und das Ergebnis einer Variant-Variable zuweisen, vor dem weitern Vorgehen dann prüfen, ob die Variantvariable einen Wert oder einen Fehler enthält.
also

dim varSVerweis as variant
varSVerweis = Application.VLookUp(...)
if VarType(varSVerweis)  vbError then ActiveCell.Value = varSVerweis

Hintergrund: mit Worksheetfunction.VLookUp bekommst du sofort einen Fehlerabbruch. Application.VLookUp erzeugt zunächst einen Fehlerwert als Ergebnis, zum Abbruch kommt es erst dann, wenn du mit diesem Fehlerwert falsch umgehst.
Daher muss das Ergebnis zunächst in eine VariantVariable und dann prüft man, was man bekommen hat bevor man weiter macht.
c) ist ähnlich wie Variante b), du suchst mit .FIND und weist das Ergebnis einer Range-Variable zu.
dann prüfst du, ob die Suche erfolgreich war:

dim rngErg as string
set rngErg = Worksheets(...).Columns(lngQuellsuchspalte).Find(what:=ActiveCell.Offset(0, -1). _
Value, lookat:=xlwhole, lookin:=xlvalues)
if not rngErg is nothing then ActiveCell.Value = rngErg.Value

wobei das alles nur bei Einzelzellen sinnvoll ist.
wenn du den gleichen SVerweis für mehrere Zellen ausführen willst, ist es auch per VBA besser, die entsprechende SVerweis-Formel in die Zellen zu schreiben und ggf danach dann Formeln durch Werte zu ersetzen.
ist deshalb schneller, weil du nicht jede Zelle einzeln bearbeiten musst, sondern die Formel in einem Schritt in alle Zellen gleichzeitig schreiben kannst.
Gruß Daniel

Forumthread: VLOOKUP über VBA

16
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial