Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VLOOKUP über VBA

VLOOKUP über VBA
01.03.2019 13:00:48
Martin
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
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VLOOKUP über VBA
01.03.2019 13:13:23
Daniel
Hi
wenn du eine Range nicht über den Addressstring, sondern über zwei Zellen bzw wie bei dir Zellbereiche definierst, dann muss die Tabellenblattangabe vor diesen beiden Zellbereichen innerhalb der Range erscheinen.
die Tabellenblattangabe vor der Range darfst du sogar weglassen.
falsch ist: Worksheeets(xxx).Range(Columns(a), Columns(b))
richtig ist: Range(Worksheets(xxx).Columns(a), Worksheets(xxx).Columns(b))
wobei ich dir hier empfehlen würde, den Zellbereich mit Hilfe der Resize-Funktion zu erzeugen:
Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte).Resize(, lngSpaltenindexSverweis)
Gruß Daniel
Anzeige
AW: VLOOKUP über VBA
01.03.2019 13:26:53
Martin
Hallo Daniel,
danke dir!
Mit der Lösung:
strSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, Range(Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte), Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellergebnisspalte)), lngSpaltenindexSverweis, False)
funktioniert es jetzt!!
Dein Vorschlag mit dem Resize habe ich so umgesetzt, dabei kommt leider weiterhin der 1004er Fehler. Aber die obere Geschichte funktioniert ja, das reicht mir, DAnke!!
strSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte).Resize(, lngSpaltenindexSverweis), False)
Anzeige
AW: VLOOKUP über VBA
01.03.2019 13:29:43
Daniel
Hi
wundert mich, dass das mit dem Resize nicht funktioniert
hast du mal getestet, ob damit auch der richtige Zellbereich angesprochen wird?
Gruß Daniel
AW: VLOOKUP über VBA
01.03.2019 13:54:58
Martin
Nein habe ich nicht, aber solange die andere Variante funktioniert würde mir das auch reichen. Jetzt hab ich nur leider noch das im vorherigen Beitrag beschriebene Problem bei fehlendem Rückgabewert aus dem SVERWEIS
Anzeige
AW: VLOOKUP über VBA
01.03.2019 14:11:57
Daniel
naja, wenn du gerne aufwendig programmierst, kannst du das tun.
das mit dem Resize ist halt einfach kürzer, vorallem wenn man wie du lange Variablennamen verwendet.
Gruß Daniel
AW: VLOOKUP über VBA
01.03.2019 20:42:02
Martin
Hallo Daniel,
es funktioniert ja dank deiner Hilfe nun so:
varSverweis = Application.VLookup(.Offset(, -1).Value, Range(Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte), Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellergebnisspalte)), lngSpaltenindexSverweis, False)
Wie müsste ich stattdessen denn die Sache mit dem Resize aufgeben? Ist so wie du sagst natürlich wesentlich kürzer. Dies hier führt nur leider zu dem 1004er Fehler:
varSverweis = Applictaion.VLookup(.Offset(, -1).Value, Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte).Resize(, lngSpaltenindexSverweis), False)
Gruß Martin
Anzeige
AW: VLOOKUP über VBA
01.03.2019 21:04:15
Daniel
Hi
einfacher Rechtschreibfehler deinerseits.
du hast das Application am Anfang falsch geschrieben.
schreibe die Codes selber und nutze dabei die IntelliSense (STRG+LEER tippen, nach dem du die ersten Buchstaben eines Befehls, Objektes oder deklarierter Variable eingegeben hast).
insbesondere bei deinen langen Variablennamen sparst du dir damit viel Tipperei und vermeidest Rechtschreibfehler.
dabei noch ein tipp: schreibe immer alles klein.
Wenn der Compiler ein Wort erkennt, wird er automatisch die Groß/Kleinschreibung anpassen ansonsten bleiben alle Buchstaben klein. Daran kannst du erkennen, ob du das Wort richtig geschrieben hast oder nicht.
Nutze bitte diese Hilfsmittel, und kopiere nicht einfach ohne nachzudenken irgendwelche Codes aus dem Internet.
Gruß Daniel
Anzeige
AW: VLOOKUP über VBA
01.03.2019 21:12:29
Daniel
achso, und den Spaltenindex für den SVerweis hast du auch vergessen, dh da fehlt beim SVerweis ein Parameter.
varSverweis = Application.VLookup(.Offset(, -1).Value, Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte).Resize(, lngSpaltenindexSverweis), lngSpaltenindexSverweis, False)
Gruß Daniel
Anzeige
AW: VLOOKUP über VBA
02.03.2019 18:34:27
Martin
Vielen Dank für deine super Hilfe!!!!!
AW: VLOOKUP über VBA
04.03.2019 07:57:36
Martin
Super, so funktioniert es auch mit dem Resize. DAnke!
AW: VLOOKUP über VBA
01.03.2019 13:53:39
Martin
Hallo Daniel, eine Frage hätte ich doch noch.
Wenn der SVERWEIS keinen Treffer erzielt, weil der Eintrag in der durchsuchten Quelldatei nicht enthalten ist kommt leider auch der 1004er Fehler mit dem ansonsten funktionierenden angepassten Code:
With ActiveCell
varSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, Range(Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte), Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellergebnisspalte)), lngSpaltenindexSverweis, False)
End With
ActiveCell.FormulaR1C1 = varSverweis
Hast du oder sonst jemand eine Idee, wie ich das verhindern kann, dass wenn der gesuchte Eintrag in der Quelldatei nicht vorkommt, es nicht zum Abbruch kommt?
Anzeige
AW: VLOOKUP über VBA
01.03.2019 14:10:57
Daniel
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
Anzeige
AW: VLOOKUP über VBA
01.03.2019 15:29:11
Martin
Danke. Probiere ich morgen mal aud
AW: VLOOKUP über VBA
01.03.2019 20:29:10
Martin
Hallo Daniel,
vielen Dank für deine Hilfe. Ich wüsste garnicht, woher man an so ein Wissen als VBA Einsteiger ran kommen soll. Habe diverse VBA Bücher, aber darin sind meist nur einfacher Beispielcode aufgeführt.
Dein Beispielcode:
dim varSVerweis as variant
varSVerweis = Application.VLookUp(...)
if VarType(varSVerweis) vbError then ActiveCell.Value = varSVerweis
Funktioniert so wunderbar. Wär ich allein nur leider nie drauf gekommen...
Z.B. anstatt Application.Worksheetfunction.VLookUp hier nur Application.VLookUp zu schreiben, wie soll man darauf kommen als Einsteiger frag ich mich. Mal abgesehen, dass mir der wirkliche Unterschied jetzt noch nicht klar ist. Habe das mit dem Worksheetfunction auch nur in irgendeinem Forum gefunden...
Du meinst es wäre einfacher und vor allem schneller "einfach" den SVERWEIS zu nutzen, um so nicht jede zu befüllende Zelle mit einer Schleife zu durchlaufen, so wie ich es jetzt gemacht habe. Das hätte ich gerne gemacht, ich weiß nur leider nicht wie :-(
Ich muss in den per VBA erstellten SVWERWEIS halt entsprechende Variablen mit einbauen um das ganze flexibel auf verschiedene eingelesene Tabellen anwenden zu können. Aber dafür reichen meine VBA Kenntnisse leider nicht.
Gruß Martin
Anzeige
AW: VLOOKUP über VBA
01.03.2019 20:31:14
Martin
Danke für den Hinweis, aber das sieht für mich als VBA Einsteiger doch recht kompliziert aus. Den SVERWEIS aus Excel zu nutzen ist für mich denke ich der "leichtere" Weg.
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige

Infobox / Tutorial

VLOOKUP über VBA: Schritt-für-Schritt-Anleitung und Tipps


Schritt-für-Schritt-Anleitung

  1. Vorbereitungen treffen: Stelle sicher, dass Du das richtige Tabellenblatt und die entsprechenden Spaltennummern hast. Definiere die Variablen, die Du für den VLOOKUP benötigst:

    Dim varSverweis As Variant
    Dim lngQuellsuchspalte As Long
    Dim lngQuellergebnisspalte As Long
    Dim lngSpaltenindexSverweis As Long
    
    lngQuellsuchspalte = 5
    lngQuellergebnisspalte = 7
    lngSpaltenindexSverweis = 3
  2. Die Range definieren: Nutze die Application.WorksheetFunction.VLookup-Funktion, um den Wert zu suchen. Achte darauf, die Range korrekt anzugeben:

    With ActiveCell
        varSverweis = Application.WorksheetFunction.VLookup(.Offset(, -1).Value, _
        Worksheets(strQuelltabelleAusDerUebersetztWird).Range( _
        Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte), _
        Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellergebnisspalte)), _
        lngSpaltenindexSverweis, False)
    End With
  3. Ergebnis in die Zelle schreiben: Setze den gefundenen Wert in die aktive Zelle:

    ActiveCell.Value = varSverweis
  4. Fehlerbehandlung: Überprüfe, ob der VLOOKUP-Befehl einen Fehler zurückgibt, und behandle dies entsprechend, um den 1004-Fehler zu vermeiden.


Häufige Fehler und Lösungen

  • Fehler 1004: Dieser Fehler tritt häufig auf, wenn die Range nicht korrekt angegeben ist. Achte darauf, dass der Tabellenblattname vor der Range korrekt ist:

    • Falsch: Worksheets(xxx).Range(Columns(a), Columns(b))
    • Richtig: Range(Worksheets(xxx).Columns(a), Worksheets(xxx).Columns(b))
  • Variable nicht definiert: Wenn Du eine Variable in der VLOOKUP-Funktion verwendest, stelle sicher, dass diese korrekt definiert ist.

  • Keine Übereinstimmung gefunden: Wenn der Wert nicht gefunden wird, kann es zu einem Fehler kommen. Verwende Application.VLookup anstelle von Application.WorksheetFunction.VLookup, um dies zu verhindern.


Alternative Methoden

  1. DLookup verwenden: Eine alternative Methode ist die Verwendung von DLookup, um Werte aus einer Datenbank oder Tabelle zu suchen.

    varSverweis = DLookup("Feldname", "Tabelle", "Kriterium=" & Wert)
  2. FIND-Methode: Du kannst auch die .Find-Methode verwenden, um nach einem Wert zu suchen:

    Dim rngErg As Range
    Set rngErg = Worksheets(strQuelltabelleAusDerUebersetztWird).Columns(lngQuellsuchspalte).Find(what:=ActiveCell.Offset(0, -1).Value)
    If Not rngErg Is Nothing Then
        ActiveCell.Value = rngErg.Value
    End If

Praktische Beispiele

  • Beispiel für VLOOKUP in VBA:

    Dim varSverweis As Variant
    varSverweis = Application.VLookup(ActiveCell.Offset(0, -1).Value, _
    Worksheets("Tabelle1").Range("A:C"), 2, False)
    
    If Not IsError(varSverweis) Then
        ActiveCell.Value = varSverweis
    Else
        ActiveCell.Value = "Nicht gefunden"
    End If

Tipps für Profis

  • IntelliSense nutzen: Schreibe den Code selbst und nutze die IntelliSense-Funktion in VBA, um Schreibfehler zu vermeiden.
  • Variablen klein halten: Verwende kurze Namen für Variablen, um die Lesbarkeit zu verbessern und Tippfehler zu vermeiden.
  • Fehlerbehandlung implementieren: Implementiere eine klare Fehlerbehandlung, um das Debuggen einfacher zu gestalten und 1004-Fehler zu vermeiden.
  • Formeln in Zellen schreiben: Wenn Du mehrere Zellen gleichzeitig befüllen musst, schreibe die VLOOKUP-Formel direkt in die Zellen, um Zeit zu sparen:
    Range("B2:B10").Formula = "=VLOOKUP(A2, Tabelle1!A:C, 2, FALSE)"

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen Application.VLookup und Application.WorksheetFunction.VLookup?
Application.VLookup gibt einen Fehler zurück, wenn kein Wert gefunden wird, während Application.WorksheetFunction.VLookup sofort einen Fehlerabbruch auslöst.

2. Wie kann ich sicherstellen, dass mein VLOOKUP nicht fehlschlägt?
Verwende If Not IsError(varSverweis) um zu überprüfen, ob ein Fehler aufgetreten ist, bevor Du den Wert verwendest.

3. Kann ich VLOOKUP in einer Schleife verwenden?
Ja, Du kannst VLOOKUP in einer Schleife verwenden, um mehrere Werte nacheinander zu suchen und in Zellen zu schreiben.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige