Vlookup ist eine Arbeitsblattfunktion in Excel, kann jedoch auch in VBA verwendet werden. Die Funktionalität von Vlookup ähnelt der Funktionalität in VBA und in Arbeitsblatt. Da es sich um eine Arbeitsblattfunktion handelt, erfolgt die Verwendung von Vlookup in VBA über Application.WorksheetFunction Methode und die Argumente bleiben gleich.
VLOOKUP-Funktion in Excel VBA
Die VLOOKUP-Funktion in Excel wird verwendet, um einen Wert in einem Array zu suchen und den entsprechenden Wert aus einer anderen Spalte zurückzugeben. Der zu suchende Wert sollte in der ersten Spalte vorhanden sein. Es muss auch angegeben werden, ob nach einer genauen Übereinstimmung oder einer ungefähren Übereinstimmung gesucht werden soll. Die Arbeitsblattfunktion VLOOKUP kann in der VBA-Codierung verwendet werden. Die Funktion ist nicht in VBA integriert und kann daher nur über das Arbeitsblatt aufgerufen werden.
Die VLOOKUP-Funktion in Excel hat die folgende Syntax:
Lookup_value ist der Wert, nach dem gesucht werden soll, table_arrray ist die Tabelle, col_index_num ist die Spaltennummer des Rückgabewerts, range_lookup gibt an, ob die Übereinstimmung genau oder ungefähr ist. range_lookup kann TRUE / FALSE oder 0/1 sein.
Im VBA-Code kann die VLOOKUP-Funktion wie folgt verwendet werden:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Wie verwende ich VLookup in Excel VBA?
Im Folgenden finden Sie einige Beispiele für VLookup-Code in Excel VBA.
Sie können diese VLookup in Excel VBA-Vorlage hier herunterladen - VLookup in Excel VBA-Vorlage
VLookup-Code in Excel VBA Beispiel 1
Lassen Sie uns sehen, wie wir die Arbeitsblattfunktion VLOOKUP in Excel VBA aufrufen können.
Angenommen, Sie haben Daten zum Studentenausweis, zum Namen und zu den von ihnen erzielten Durchschnittsnoten.
Nun möchten Sie die Noten eines Schülers mit der ID 11004 nachschlagen.
Führen Sie die folgenden Schritte aus, um nach dem Wert zu suchen:
- Gehen Sie zur Registerkarte Entwickler und klicken Sie auf Visual Basic.
- Gehen Sie im VBA-Fenster zu Einfügen und klicken Sie auf Modul.
- Schreiben Sie nun den VBA VLOOKUP-Code. Der folgende VBA VLOOKUP-Code kann verwendet werden.
Sub vlookup1 ()
Dim student_id As Long
Dimmspuren so lange
student_id = 11004
Setze myrange = Range ("B4: D8")
markiert = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Definieren Sie zunächst eine Schüler-ID, die der zu suchende Wert ist. Deshalb definieren wir,
student_id = 11004
Als nächstes definieren wir den Bereich, in dem der Wert und der Rückgabewert existieren. Da unsere Daten in den Zellen B4: D8 vorhanden sind, definieren wir einen Bereichsbereich als:
Setze myrange = Range ("B4: D8")
Schließlich geben wir die VLOOKUP-Funktion mithilfe der Arbeitsblattfunktion in eine Variable ein und markieren sie als:
markiert = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Verwenden Sie den folgenden Befehl, um die Markierungen in einem Meldungsfeld zu drucken:
MsgBox "Student mit Ausweis:" & student_id & "erhalten" & Noten & "Noten"
Es wird zurückkehren:
Student mit Ausweis: 11004 erhielt 85 Punkte.
Klicken Sie nun auf die Schaltfläche Ausführen.
Sie werden feststellen, dass in der Excel-Tabelle ein Meldungsfeld angezeigt wird.
VLookup-Code in Excel VBA Beispiel 2
Angenommen, Sie haben die Daten der Namen der Mitarbeiter und deren Gehalt. Diese Daten erhalten die Spalten B und C. Nun müssen Sie einen VBA VLOOKUP-Code schreiben, sodass bei einem Namen des Mitarbeiters in einer Zelle, F4, das Gehalt des Mitarbeiters in der Zelle G4 zurückgegeben wird.
Schreiben wir den VBA VLOOKUP-Code.
- Definieren Sie den Bereich, in dem die Werte vorhanden sind, dh Spalte B und C.
Setze myrange = Range ("B: C")
- Definieren Sie den Namen des Mitarbeiters und geben Sie den Namen aus der Zelle F4 ein.
Set name = Range ("F4")
- Definieren Sie das Gehalt als Zelle G4.
Gehalt einstellen = Bereich ("G4")
- Rufen Sie nun die VLOOKUP-Funktion mit WorksheetFunction in VBA auf und geben Sie sie in Gehalt ein. Dies gibt den Wert (Ausgabe der Vlookup-Funktion) in der Zelle G4 zurück. Die folgende Syntax kann verwendet werden:
Gehalt.Wert = Application.WorksheetFunction.VLookup (Name, Myrange, 2, False)
- Führen Sie nun das Modul aus. Die Zelle G4 enthält das Gehalt des Mitarbeiters, nachdem Sie den VBA VLOOKUP-Code ausgeführt haben.
Angenommen, Sie ändern den Wert von Zelle F4 im Arbeitsblatt in „David“ und führen den Code erneut aus. Dadurch wird das Gehalt von David zurückgegeben.
VLookup-Code in Excel VBA Beispiel 3
Angenommen, Sie haben die Daten des Mitarbeiters Ihres Unternehmens mit ID, Namen, Abteilung und Gehalt. Mit Vlookup in VBA möchten Sie die Gehaltsdetails eines Mitarbeiters anhand seines Namens und seiner Abteilung abrufen.
Da die vlookup-Funktion in Excel den lookup_value nur in einer einzigen Spalte durchsucht, die die erste Spalte des table_array ist, müssen Sie zuerst eine Spalte erstellen, die den "Namen" und die "Abteilung" jedes Mitarbeiters enthält.
Fügen Sie in VBA die Werte "Name" und "Abteilung" in Spalte B des Arbeitsblatts ein.
Wechseln Sie dazu zur Registerkarte Entwickler und klicken Sie auf Visual Basic. Gehen Sie dann zum Einfügen und klicken Sie auf Modul, um ein neues Modul zu starten.
Schreiben wir nun Code so, dass Spalte B die Werte von Spalte D (Name) und Spalte E enthält.
Die Syntax lautet wie folgt:
Verwenden Sie zunächst eine 'for'-Schleife von i = 4, da die Werte in diesem Fall ab der 4. Zeile beginnen. Die Schleife wird bis zum Ende der letzten Zeile der Spalte C fortgesetzt. Die Variable i kann also als Zeilennummer innerhalb der 'for'-Schleife verwendet werden.
Geben Sie dann den Wert für Cell (Zeilennummer, Spalte B) ein, der als Zellen (i, "B") angegeben werden kann. Wert als Zelle (Zeilennummer, Spalte D) & "_" & Zelle (Zeilennummer, Spalte E. ).
Angenommen, Sie möchten die Zelle B5 = D5 & "_" & E5 zuweisen, können Sie den Code einfach wie folgt verwenden:
Zellen (5, "B"). Wert = Zellen (5, "D"). Wert & "_" & Zellen (5, "E"). Wert
Um nun nach dem Suchwert im Array B5: E24 zu suchen, müssen Sie zuerst den Suchwert eingeben. Nehmen wir den Wert (Name und Abteilung) vom Benutzer. Um dies zu tun,
- Definieren Sie drei Variablen, Name, Abteilung und Lookup_Val als Zeichenfolge.
- Nehmen Sie die Namenseingabe vom Benutzer. Verwenden Sie den Code:
name = InputBox ("Geben Sie den Namen des Mitarbeiters ein")
Der Inhalt im Eingabefeld "Enter the .." wird im Eingabeaufforderungsfeld angezeigt, wenn Sie den Code ausführen. Die in der Eingabeaufforderung eingegebene Zeichenfolge wird der Namensvariablen zugewiesen.
- Nehmen Sie die Abteilung vom Benutzer. Dies kann ähnlich wie oben erfolgen.
Abteilung = InputBox ("Geben Sie die Abteilung des Mitarbeiters ein")
- Weisen Sie der Variablen lookup_val den Namen und die Abteilung mit "_" als Trennzeichen mit der folgenden Syntax zu:
lookup_val = Name & "_" & Abteilung
- Schreiben Sie die vlookup-Syntax, um im Bereich B5 nach dem lookup_val zu suchen: E24 geben Sie es in einem variablen Gehalt zurück.
Initialisieren Sie das variable Gehalt:
Dim Gehalt so lange
Verwenden Sie die Vlookup-Funktion, um das lookup_val zu finden. Das table_array kann als Range ("B: F") angegeben werden und das Gehalt ist in der 5. Spalte angegeben. Die folgende Syntax kann daher verwendet werden:
Gehalt = Application.WorksheetFunction.VLookup (lookup_val, Bereich ("B: F"), 5, False)
- Verwenden Sie die folgende Syntax, um das Gehalt in einem Meldungsfeld auszudrucken:
MsgBox (Das Gehalt des Mitarbeiters beträgt ”& Gehalt)
Führen Sie nun den Code aus. Im Arbeitsblatt wird ein Eingabeaufforderungsfeld angezeigt, in das Sie den Namen eingeben können. Nachdem Sie den Namen eingegeben haben (Say Sashi) und auf OK klicken.
Es wird ein weiteres Feld geöffnet, in das Sie die Abteilung eingeben können. Nachdem Sie die Abteilung betreten haben, sagen Sie IT.
Es wird das Gehalt des Mitarbeiters gedruckt.
Wenn das Vlookup einen Mitarbeiter mit dem Namen und der Abteilung findet, wird ein Fehler ausgegeben. Angenommen, Sie geben den Namen "Vishnu" und die Abteilung "IT" an, dann wird der Laufzeitfehler "1004" zurückgegeben.
Um dieses Problem zu beheben, können Sie im Code angeben, dass bei diesem Fehlertyp stattdessen "Wert nicht gefunden" gedruckt wird. Um dies zu tun,
- Verwenden Sie vor der Verwendung der vlookup-Syntax den folgenden Code:
Bei Fehler GoTo-Nachricht
Prüfen:
Der nachfolgende Code (von Check :) wird überwacht, und wenn ein Fehler auftritt, wird die Anweisung "message" aufgerufen
- Geben Sie am Ende des Codes (Before End Sub) an, dass bei einer Fehlernummer von 1004 im Meldungsfeld "Mitarbeiterdaten nicht vorhanden" gedruckt werden soll. Dies kann mithilfe der folgenden Syntax erfolgen:
Botschaft:
Wenn Err.Number = 1004 Dann
MsgBox ("Mitarbeiterdaten nicht vorhanden")
End If
Modul 1:
Sub vlookup3 ()Für i = 4 To Cells (Rows.Count, "C"). End (xlUp) .Row
Zellen (i, "B"). Wert = Zellen (i, "D"). Wert & "_" & Zellen (i, "E"). Wert
Nächster iDim-Name als Zeichenfolge
Abteilung als String dimmen
Dim lookup_val As String
Dim Gehalt als Langname = InputBox ("Geben Sie den Namen des Mitarbeiters ein")
Abteilung = InputBox ("Geben Sie die Abteilung des Mitarbeiters ein")
lookup_val = name & "_" & departmentOn Error GoTo Message
prüfen:
Gehalt = Application.WorksheetFunction.VLookup (lookup_val, Bereich ("B: F"), 5, False)
MsgBox ("Das Gehalt des Mitarbeiters ist" & Gehalt) Nachricht:
Wenn Err.Number = 1004 Dann
MsgBox ("Mitarbeiterdaten nicht vorhanden")
End IfEnd Sub
Wichtige Informationen zu VLookup in Excel VBA
- Die Vlookup-Funktion kann in Excel VBA mithilfe von WorksheetFunction aufgerufen werden.
- Die Syntax der vlookup-Funktion bleibt in Excel VBA unverändert.
- Wenn der VBA-Lookup-Code den Lookup-Wert nicht finden kann, wird ein 1004-Fehler ausgegeben.
- Der Fehler in der vlookup-Funktion kann mit einer goto-Anweisung verwaltet werden, wenn ein Fehler zurückgegeben wird.
