So automatisieren Sie Vlookup mit Excel VBA

Vlookup ist eine wesentliche Funktion in Excel und hat sich zu einem wichtigen Bestandteil der Datenverarbeitung entwickelt. Es bietet eine Reihe von Funktionen, die Sie mit einer umfassenden Datenbank kombinieren können.

Wenn Sie diese Funktion jedoch automatisieren möchten, können Sie dies tun, insbesondere wenn Sie wissen, wie man die in Excel und VBA verfügbare Automatisierungssprache verwendet. So können Sie die Vlookup-Funktion in Excel VBA automatisieren .

Vlookup-Funktionsformel in Excel

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Bedeutung der Argumente in der Formel:

• lookup_value: Der zugrunde liegende Wert, den Sie im Array suchen möchten.
• lookup_array: Quelldaten, Lookup_Value werden als Referenz verwendet.
• column_index: Spalte gibt Wert zurück.
• exact_match/partial_match: Verwenden Sie 0 oder 1, um den Kombinationstyp anzugeben.

VBA-Vlookup-Funktion in Excel

Die Verwendung der Vlookup-Funktion unterscheidet sich nicht wesentlich, wenn Sie sie direkt in Excel oder über VBA verwenden. Unabhängig davon, ob ein Datenanalyst Informationsreihen verarbeitet oder ein Lagerverwalter ständig mit neuen Produkten arbeitet, ist die Vlookup-Funktion nützlich.

Wenn Sie mit einem dynamischen Suchbereich arbeiten, ist es am besten, die Formel zu automatisieren, um zu vermeiden, dass Sie dieselbe Formel mehrmals in Excel bearbeiten müssen. Durch die Automatisierung der Vlookup-Funktion in VBA können Sie mehrspaltige Berechnungen mit einem Klick durchführen.

Illustration

Dieser Datensatz besteht aus zwei Teilen: einer Nachschlagetabelle und Zieldatenpunkten. Die Nachschlagetabelle enthält drei Spalten mit Daten in den beiden Feldern Unterkategorie und Produktname:

Die Zieltabelle enthält die entsprechenden Spalten, keine Unterordner oder Produktnamendaten. Beachten Sie, dass die Werte in der Spalte „Bestell-ID“ auch in derselben Spalte in der Nachschlagetabelle erscheinen:

1. Nehmen Sie die erforderlichen Einstellungen vor

Öffnen Sie zunächst den Code-Editor (drücken Sie Alt + F11 oder navigieren Sie zur Registerkarte „Entwickler “) in einer neuen Excel-Arbeitsmappe und fügen Sie das Modul hinzu, um mit dem Codieren zu beginnen. Fügen Sie im Code-Editor die folgenden Zeilen oben im Programmierfenster hinzu, um eine Unterroutine zu erstellen:

Sub vlookup_fn1()End Sub

Die Unterroutine ist ein Container für VBA-Code und wird für die erfolgreiche Ausführung benötigt. Eine weitere Alternative besteht darin, ein VBA-Benutzerformular zu erstellen, um die Benutzeroberfläche interaktiver zu gestalten.

2. Variablen deklarieren und Referenzbereiche erstellen

Zuerst müssen Sie die Datentypen der Variablen mit dem Dim-Befehl deklarieren:

Dim i as integer, lastrow as long

Erstellen Sie als Nächstes eine Variable lastrow , um den Wert der zuletzt ausgefüllten Zeile im Suchbereich zu speichern. Die Funktion lastrow verwendet die Funktion end(xldown) , um die letzte Zeilenreferenz in einem bestimmten Bereich zu berechnen.

In diesem Beispiel enthält die Variable lastrow den Wert der letzten gefüllten Zeile im Suchbereich, insbesondere 17.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

Im obigen Beispiel erstreckt sich die Zieltabelle von A2:C17 und die Quelltabelle von F2:H17. Die vlookup-Formel durchläuft jeden Wert in Spalte A, sucht ihn in der Quelltabelle und fügt die kombinierten Einträge in die Spalten B und C ein.

Bevor Sie jedoch in die Schleife eintreten, müssen Sie die Bereichswerte wie folgt in einer neuen Variablen ( my_range ) speichern:

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Sie müssen den Startzellenbezug (F3) und den Endspaltenbezug (H) explizit angeben. VBA fügt automatisch Zeilenwerte hinzu, um das Sucharray zu vervollständigen.

3. Schreiben Sie eine Schleife, um jeden Suchwert zu durchlaufen

Definieren Sie vor dem Schreiben der Schleife den Startzeilenwert 2. Das Definieren eines Startwerts für die Schleife ist erforderlich, wenn Sie mit dynamischen Komponenten arbeiten. In der Zieltabelle ist Zeile 2 die erste Datenzeile. Ändern Sie diesen Wert, wenn die Daten in einer anderen Zeile beginnen.

i = 2

Sie können eine do-while- Schleife verwenden , um jede Zeile zu verarbeiten, beginnend bei Zeile 2 und endend bei Zeile 17. Wie der Rest des Codes ist dieser Aspekt flexibel; Die Schleife wird so lange ausgeführt, bis die Bedingung falsch ist. Verwenden Sie die Bedingung, um zu testen, ob in der ersten Zelle der aktuellen Zeile ein nicht leerer Wert vorhanden ist.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Innerhalb der Schleife können Sie die Vlookup-Funktion aufrufen, um die Zellen zu füllen:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Diese Befehle legen die Werte von Zellen in der aktuellen Zeile in den Spalten 2 bzw. 3 fest. Sie verwenden das WorksheetFunction-Objekt, um die Vlookup-Funktion aufzurufen und übergeben den entsprechenden Wert aus Spalte 1 an die Suche. Sie übergeben außerdem den von Ihnen ursprünglich definierten Bereich und den Index der relevanten Spalte, um den endgültigen Wert abzurufen.

Der vlookup-Code ist fertig, aber Sie müssen die Zeilenvariable immer noch jedes Mal in der Schleife erhöhen:

i = i + 1

Jedes Mal, wenn die Schleife ausgeführt wird, erhöht sie den Wert i um 1 . Denken Sie daran, dass der Startzeilenwert 2 ist und die Erhöhung jedes Mal erfolgt, wenn die Schleife ausgeführt wird. Verwenden Sie das Schlüsselwort „loop“ , um das Ende dieses Codeblocks festzulegen.

Wenn der gesamte Code ausgeführt wird, werden beide Spalten basierend auf den Werten in der Quelltabelle mit Ergebnissen gefüllt.

Nachfolgend finden Sie den gesamten Referenzcode:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Erstellen Sie eine Schaltfläche zum Ausführen des Codes

Sie können in einer Excel-Tabelle eine Schaltfläche erstellen, um sie mit einem Klick auszuführen. Fügen Sie die gewünschte Form in die leere Excel-Tabelle ein, klicken Sie mit der rechten Maustaste darauf und klicken Sie auf die Option Makro zuweisen .

Wählen Sie im Dialogfeld den Namen der Unterroutine aus und klicken Sie auf OK .

Wenn Sie den Code ausführen möchten, klicken Sie auf die Schaltfläche, um zu sehen, wie die Daten sofort ausgefüllt werden.

Oben erfahren Sie, wie Sie Excel VBA verwenden, um Nachschlagefunktionen zu automatisieren . Ich hoffe, der Artikel ist für Sie nützlich.