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

VLOOKUP - Mit tabellenspalte (VBA)

Forumthread: VLOOKUP - Mit tabellenspalte (VBA)

VLOOKUP - Mit tabellenspalte (VBA)
12.01.2022 15:28:04
Herbert
Hey,
meine Problematik liegt darin,
das ich einen V-LookUP mit einer Tabellespalte füllen möchte.
Diese soll aber nicht in .Range("A10:C100") geschrieben sein,
sondern so, dass ich die Tabelle anspreche "Tabellenname".
Dies soll dafür sorgen,
das neu hinzugefügte Einträge sofort in der Dropdown aufzufinden sind.
Als Beispiel:

=SVERWEIS(AchsbildSZMZelle;Tabelle1;2;FALSCH)
Ich bin jedem für seine Hilfe dankbar!
MfG
Herbert
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VLOOKUP - Mit tabellenspalte (VBA)
12.01.2022 15:40:41
ChrisL
Hi Herbert
Ganz klar ist mir die Frage nicht. Ich interpretiere, dass du den Blattnamen von der Matrix in eine Variable auslagern möchtest.

strTBname = "Tabelle1"
MsgBox WorksheetFunction.VLookup(Range("AchsbildSZMZelle"), Worksheets(strTBname).Range("A10:C100"), 2, 0)
cu
Chris
Anzeige
AW: VLOOKUP - Mit tabellenspalte (VBA)
12.01.2022 16:30:12
Herbert
Hallo Chris,
soweit hast du alles richitg interpretiert,
nur möchte ich jetzt noch das er nicht direkte Zellbezüge als Range angegeben hat,
sondern die Tabellennamen an sich,
Als Beispiel:
ich erstelle eine tabelle mit Strg + t und nenne diese "tbl_Fahrzeuge",
nun soll er nicht den Zellbezug von oben (.Range("A10:C100")) als Range nehmen,
sondern die tabelle ("tbl_Fahrzeuge"),
damit neu Einträge auch direkt in die Dropdown mit einfließen können.
Danke für deine Antwort!
MfG
Herbert
Anzeige
AW: VLOOKUP - Mit tabellenspalte (VBA)
12.01.2022 17:11:29
ChrisL
Hi Herbert
Wir sprechen also nicht vom Namen des (Tabellen-)blattes, sondern von definierten Namen (Namensmanager).
Somit:
Range("MeinName")
oder
s = "MeinName"
Range(s)
cu
Chris
AW: VLOOKUP - Mit tabellenspalte (VBA)
12.01.2022 18:39:12
Yal
Moin zusammen,
ListObject, also "aktive" oder "intelligente" Tabelle, wie sie als VBA-Objekt benannt werden, sind ein bischen schwer zu fassen. Der Makrorekorder liefert die Formel in Excel-, aber nicht in VBA-Form. Na gut, man könnte "Evaluate" verwenden.
Man kann das Problem umgehen, in dem man anstatt einen VLookup einen Dictionary aufbaut. Hört sich kompliziert an, ist es aber nicht.
Der Verweis auf "Microsoft Scripting Runtime" nicht vergessen.

Sub Makro2()
'Unter Extras, Verweise... auf Microsoft Scripting Runtime
Dim Dict As New Scripting.Dictionary
Dim E
On Error Resume Next 'zweite Dictionary-Eintrag auf demselben Schlüssel werden ignoriert
With Worksheets("Tabelle1")
'Aufbau der Dictionary
For Each E In .ListObjects("Tabelle2").DataBodyRange.Rows
Dict.Add E.Columns(1).Value, E.Columns(2).Value 'Key, Value
Next
'Übersetzung, also Verwendung der Dictionary
For Each E In .Range("J2:J7").Cells 'J2:J7 ist meine persönlichen Testbereich: eine Kopie der ersten Spalte vom ListObject
E.Offset(0, 1) = Dict(E.Value)
Next
End With
End Sub
Bei Bedarf den Dictionary als Modul-globale Variable legen.
Auf einer Rückmeldung freut man sich immer.
VG
Yal
Anzeige
AW: VLOOKUP - Mit tabellenspalte (VBA)
13.01.2022 08:27:04
ChrisL
Hi Yal
Danke für die Ergänzung, aber ich sehe das Problem nicht ganz.
Herbert könnte zwar mit ListObjects arbeiten, aber eine zwingende Voraussetzung sehe ich nicht. Einerseits erhalten "intelligente" Tabellen immer automatisch einen Namen den man ansprechen kann, andererseits könnte man auch konventionell mit Zellbezügen arbeiten.

MsgBox WorksheetFunction.VLookup(Range("NameSuchkriterium"), Range("NameIntelligenteTB"), 2, 0)

With Worksheets("Tabelle1")
MsgBox WorksheetFunction.VLookup(.Range("E1"), .Range("A2:B4"), 2, 0)
End With
Dein Beispiel ist natürlich hübsch und durchaus ein gangbarer/guter Weg, aber grundsätzlich kann die "intelligente" Tabelle auch einfacher erfasst werden. Oder übersehe ich etwas?
cu
Chris
Anzeige
Perfekt, neues Problem
13.01.2022 09:18:56
Herbert
Guten Morgen Ihre zwei,
erst einmal danke für eure Bemühungen,
also ich bin ehrlich,
die Lösung von Chris gefällt von der Struktur her,
das ich es wie ein Deutschbuch lesen kann.
Die von Yal sieht sehr kompliziert aus,
soweit bin ich glaube ich nicht mit der Materie vertraut.
Aber ansonsten klappt das schon mit Chris seiner Lösung was ich vor hatte.
Ich habe hier jetzt noch einen VLookUp,
wo er mir sagt:
Laufzeitfehler '1004':
Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.

VLookUP:

WorksheetFunction.VLookup(cbox_kennzeichen.Value, Worksheets("Fuhrpark").Range(Location), 2, 0).Value
Danke für eure Hilfen!
MfG
Herbert
Anzeige
AW: Perfekt, neues Problem
13.01.2022 10:24:14
Yal
Hallo Herbert,
@Chris: ja, da hast Du recht. ich habe mich auf der aktiven Tabelle fixiert, was nicht unbedingt sein muss. Es bleibt an der Stelle die Frage von Herbert offen: sicherstellen, dass diese benannte Bereich sich mit jede neuer Zeile in der aktiven Tabelle sich ergänzt.
@Herbert:
ich habe verschiedene Versuche unternommen, der Namer der aktive Tabelle in einem VLookup reinzubringen. Hier das einzige, was erfolgreich war:

Sub Makro3()
Dim Z As Range
With Worksheets("Tabelle1")
For Each Z In .Range("J2:J7").Cells
Z.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Tabelle2,2,0)"
Z.Offset(0, 1) = Z.Offset(0, 1).Value
Next
End With
End Sub
Es wird einfach einen SVerweis in der Tabelle reingebracht, berechnet und dann durch wert ersetzt.
VG
Yal
Anzeige
AW: Perfekt, neues Problem
13.01.2022 11:00:59
ChrisL
Hi Herbert, hi Yal
Irgendwie habe ich den Faden verloren, aber ich versuche mal alles auf einmal abzufackeln :)
Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden

WorksheetFunction.VLookup(cbox_kennzeichen.Value, Worksheets("Fuhrpark").Range(Location), 2, 0).Value
Hier müsste vermutlich nur das Value am Ende gelöscht werden.
Es bleibt an der Stelle die Frage von Herbert offen: sicherstellen, dass diese benannte Bereich sich mit jede neuer Zeile in der aktiven Tabelle sich ergänzt.
Mit einer "intelligenten" Tabelle ist dies bereits gegeben. Im VBA Code müsste man dann den Bereichsnamen verwenden, was ganz generell sinnvoll ist.
der Name der aktive Tabelle in einem VLookup reinzubringen
Ich gehe davon aus, dass wir mit definierten Namen (Namensmanager) arbeiten. Hierbei ist zu beachten, dass bestimmte Namen für die ganze Mappe definiert werden können oder der Name ist nur für ein bestimmtes Blatt gültig. Bei Variante 1 braucht es keinen Bezug zum Blatt und Range("Name") ohne Blattbezug reicht, weil dieser Bezug bereits in der Namensdefinition hergestellt wurde. Diese Variante ist für "intelligente" Tabellen Standard. Im anderen Fall könnte man dann auch noch den Blattnamen einbeziehen Worksheets(xy).Range("Name"), aber dies wird hier m.E. nicht benötigt.
Randbemerkung, das Beispiel Makro 3 liesse sich noch optimieren:

Sub Makro3()
With Worksheets("Tabelle1").Range("J2:J7").Offset(0, 1)
.FormulaR1C1 = "=VLOOKUP(RC[-1],Tabelle2,2,0)"
.Value = .Value
End With
End Sub
cu
Chris
Anzeige
.Value (klappt nicht)
13.01.2022 14:22:25
Herbert
Moin Chris,
danke erst einmal für deine ganzen Antworten,
die Tabelle ist eine Intellegente Tabelle,
also nicht eine so erstellte aus Rahmen usw.,
sondern über STRG + T und wenn neue Einträge werden diese manuell eingetragen.
Ich möchte nur nicht jedes mal wenn ein neuer Eintrag kommt,
die kompletten Zellbezüge im VBA-Code ändern müssen,
weil das auf langer Hinsicht sehr anstrengend ist und ich müsste beim löschen,
wenn z.B. ein Fahrzeug verkauft würde,
wieder die Zellbezüge ändern.
Der nächste Punkt ist, dass ich gefühlt der einzige bin,
der VBA benutzt, weil viele meiner Kollegen in diesem Punkt bezüglich Excel nicht soweit fortgeschritten sind.
Zum Thema (.Value):
Nach dem ich das .Value entfernt habe,
kommt dennoch immer der selber fehler: "das WorksheetFunction Objekt kann nicht zugeordnet werden".
Code:

Private Sub cmdbtn_select_Click()
If cbox_kfzart.Value = "Zugmaschine" Then
strTBname = "SZM"
StartCount = 4
Letter = "AT"
AnzahlSZM = Worksheets("Hilfstabellen").Range("Count_SZM").Value + 3
Else
strTBname = "Auflieger"
StartCount = 7 + Worksheets("Hilfstabellen").Range("Count_SZM").Value
Letter = "DY"
AnzahlSZM = Worksheets("Hilfstabellen").Range("Count_Auflieger").Value + 7 + Worksheets("Hilfstabellen").Range("Count_SZM").Value
End If
Location = "A" & StartCount & ":" & Letter & AnzahlSZM
MsgBox "Erg.: " & WorksheetFunction.VLookup(cbox_kennzeichen.Value, Worksheets("Fuhrpark").Range(Location), 2, 0)
End Sub
Das wäre der vollständige Code,
warum ich die Anzahlen addiere?
Weil die Tabellen sich untereinander befinden,
für die kalkulatorische Ermittlung dessen benutze ich Zellen im Tabellenblatt,
die per Namensmanager definiert sind.
Der Letzte Teil im Code (die MessageBox) ist der Code der dieses Problem abwirft.
Danke für eure Hilfen!
MfG
Herbert
Anzeige
AW: .Value (klappt nicht)
13.01.2022 15:18:33
ChrisL
Hi Herbert
Es fällt mir schwer, den Code ohne Beispieldatei nachzuvollziehen.
Die Fehlermeldung bedeutet, dass der Suchbegriff nicht gefunden wurde. Mögliche Ursachen:
- Variable Location stimmt nicht. Du definierst Spalten A:AT und A:DY. Im SVerweis beziehst du aber immer die 2. Spalte (B). Ob der Tabellenbereich bis AT oder DY geht hat also gar keinen Einfluss.
- Der Datentyp stimmt nicht überein, vielleicht benötigst du cbox_kennzeichen.Text anstelle von Value
cu
Chris
Anzeige
AW: .Value (klappt nicht)
13.01.2022 16:23:40
GerdL
Hallo,
ich habe das ziemlich neutral formuliert, zum Ein- oder Umbauen.

Sub Unit()
Dim Blatt As Worksheet
Dim Tabellchen As ListObject
Dim Bereich As Range
Dim Suchbegriff
Dim Wertspaltenummer As Integer
Set Blatt = ActiveSheet
Set Tabellchen = Blatt.ListObjects("Tabelle1")
Set Bereich = Tabellchen.DataBodyRange
Suchbegriff = "Test"
Wertspaltenummer = 2
MsgBox WorksheetFunction.VLookup(Suchbegriff, Bereich, Wertspaltenummer, 0)
End Sub
Gruß Gerd
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

VLOOKUP mit Tabellenspalte in VBA


Schritt-für-Schritt-Anleitung

Um in VBA einen VLOOKUP (SVERWEIS) mit einer Tabellenspalte zu verwenden, kannst du folgende Schritte befolgen:

  1. Tabellen definieren: Stelle sicher, dass deine Daten in einer „intelligenten“ Tabelle (ListObject) sind. Du kannst eine solche Tabelle erstellen, indem du die Daten auswählst und Strg + T drückst.

  2. VBA-Editor öffnen: Drücke Alt + F11, um den VBA-Editor zu öffnen.

  3. Neues Modul erstellen: Klicke mit der rechten Maustaste auf „VBAProject (deineArbeitsmappe)“ und wähle Einfügen > Modul.

  4. VBA-Code einfügen: Füge den folgenden Code ein, um den VLOOKUP in VBA zu verwenden:

    Sub VLookupMitTabelle()
        Dim strTBname As String
        Dim Suchwert As Variant
        Dim Ergebnis As Variant
    
        strTBname = "tbl_Fahrzeuge" ' Der Name deiner Tabelle
        Suchwert = Range("A1").Value ' Der Suchwert
    
        Ergebnis = Application.WorksheetFunction.VLookup(Suchwert, Worksheets("Tabelle1").ListObjects(strTBname).DataBodyRange, 2, False)
    
        MsgBox Ergebnis
    End Sub
  5. Makro ausführen: Schließe den VBA-Editor und führe das Makro aus, um den VLOOKUP zu testen.


Häufige Fehler und Lösungen

  • Fehler: Laufzeitfehler '1004': Dies tritt auf, wenn der Suchwert nicht gefunden wird. Stelle sicher, dass der Suchwert in der entsprechenden Spalte der Tabelle vorhanden ist.

  • Fehler: „Die VLookup-Eigenschaft des WorksheetFunction-Objekts kann nicht zugeordnet werden“: Überprüfe, dass du .Value am Ende des VLOOKUP nicht verwendest. Der Befehl sollte wie folgt aussehen:

    MsgBox Application.WorksheetFunction.VLookup(...)
  • Variable Location stimmt nicht: Achte darauf, dass der definierte Bereich korrekt ist und tatsächlich die Daten enthält, die du suchst.


Alternative Methoden

  • Verwendung eines Dictionaries: Anstelle von VLOOKUP kannst du ein Dictionary verwenden, um die Daten schneller zu suchen. Beispiel:

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    
    ' Fülle das Dictionary
    For Each Zelle In Worksheets("Tabelle1").ListObjects("tbl_Fahrzeuge").DataBodyRange.Rows
        Dict.Add Zelle.Cells(1, 1).Value, Zelle.Cells(1, 2).Value
    Next Zelle
    
    ' Suche im Dictionary
    If Dict.Exists(Suchwert) Then
        MsgBox Dict(Suchwert)
    Else
        MsgBox "Wert nicht gefunden"
    End If

Praktische Beispiele

Hier sind einige praktische Beispiele, wie du den VLOOKUP in VBA effektiv nutzen kannst:

  1. VLOOKUP in einer Dropdown-Liste: Wenn du eine Dropdown-Liste füllst, kannst du den VLOOKUP verwenden, um dynamisch Werte zu suchen und anzuzeigen.

  2. VLOOKUP auf ein anderes Tabellenblatt: Du kannst den VLOOKUP auch verwenden, um Daten aus einem anderen Tabellenblatt zu suchen:

    Ergebnis = Application.WorksheetFunction.VLookup(cbox_kennzeichen.Value, Worksheets("Fuhrpark").Range("A:B"), 2, False)

Tipps für Profis

  • Verwende Namensbereiche: Definiere benannte Bereiche für deine Tabellen, um die Lesbarkeit und Wartbarkeit deines Codes zu verbessern.

  • Fehlerbehandlung einfügen: Implementiere eine Fehlerbehandlung, um unerwartete Fehler abzufangen:

    On Error Resume Next
    Ergebnis = Application.WorksheetFunction.VLookup(...)
    If Err.Number <> 0 Then
        MsgBox "Fehler aufgetreten: " & Err.Description
    End If
  • Optimierung der Performance: Bei großen Datenmengen kann das Arbeiten mit Dictionaries schneller sein als VLOOKUP.


FAQ: Häufige Fragen

1. Wie kann ich den VLOOKUP in VBA auf eine intelligente Tabelle anwenden? Du kannst den VLOOKUP auf eine intelligente Tabelle anwenden, indem du den Namen der Tabelle und den Bereich ListObjects verwendest.

2. Was ist der Unterschied zwischen VLOOKUP und SVERWEIS? VLOOKUP ist die englische Bezeichnung für die Funktion SVERWEIS. Der Hauptunterschied liegt in der Sprache, aber die Funktionalität bleibt gleich.

3. Kann ich VLOOKUP mit mehreren Bedingungen verwenden? VLOOKUP unterstützt keine direkten Mehrfachbedingungen. Du kannst jedoch die Funktion in Kombination mit anderen Funktionen oder einem Filter verwenden, um das gewünschte Ergebnis zu erzielen.

4. Wie kann ich die Fehlerbehandlung für VLOOKUP implementieren? Verwende On Error Resume Next, um Fehler zu ignorieren, und überprüfe anschließend mit Err.Number, ob ein Fehler aufgetreten ist.

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