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

Forumthread: Sverweis per VBA

Sverweis per VBA
12.08.2005 16:21:01
Thomas
Hallo zusammen,
vielleicht kann mir jemand bei folgendem Problem behilflich sein:
Ich möchte einen sverweis per VBA ausführen. Einem Wert aus der Spalte A ( Zelle A1 ) möchte ich per sverweis einen Wert aus einer sog. "Testmatrix" in der Zelle B1 zuordnen. Es sollen nur Werte in B1 eingefügt werden - keine Formeln !!
Außerdem soll für jeden Wert in Spalte A diese Prozedur durchgeführt werden. Die Anzahl der Werte in Spalte A ist variabel. Demnach sollte die VBA Programmierung auch variable gestaltet sein.
Danach müssen elf weitere Spalten mit sverweisen gefüllt werden (Spalten C - M). Das Prinzip ist das gleiche wie in Spalte B.
Ich bitte dringend um Hilfe.
Vielen Dank an alle im Voraus.
Gruß,
Thomas
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis per VBA
12.08.2005 16:25:19
Matthias
Hallo Thomas,
in VBA heißt SVERWEIS WorksheetFunction.VLookup().
VIelleicht kannst du mal eine Beispieltabelle hochladen, ich kann mir die Matrix in B1 nicht so ganz vorstellen ;-)
Gruß Matthias
AW: Sverweis per VBA
12.08.2005 16:30:11
Thomas
Hallo Matthias,
das gingt ja flott....
Die Matrix, aus der ein Wert per sverweis zugeordnet werden soll, heißt "Testmatrix" ! Das Ergebnis des sverweise soll in Zelle B1 stattfinden.
Kann leider keine Beispielmappe liefern. Geht es auch so ?
Thomas
Anzeige
AW: Sverweis per VBA
12.08.2005 16:42:12
Matthias
Hallo Thomas,
so?

Sub test()
Dim z As Long, lz As Long, s As Integer
lz = Range("A65536").End(xlUp).Row
If Range("A65536") <> "" Then lz = 65536
On Error Resume Next
For z = 1 To lz 'Zeilen
For s = 2 To 2 'Spalten, kann erweitert werden
Cells(z, s).Value = WorksheetFunction.VLookup(Cells(z, 1).Value, Range("Testmatrix"), s, False)
If Err.Number > 0 Then
Err.Clear
Cells(z, s) = "#NV!"
End If
Next s
Next z
End Sub

Gruß Matthias
Anzeige
AW: Sverweis per VBA
15.08.2005 07:55:45
Thomas
Hallo Matthias,
das passt soweit ganz gut. Wenn noch Probleme auftreten, melde ich mich wieder.
Vielen Dank.
Gruß,
Thomas
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Sverweis mit VBA umsetzen


Schritt-für-Schritt-Anleitung

Um einen SVERWEIS in VBA umzusetzen, kannst Du die folgende Schritt-für-Schritt-Anleitung befolgen:

  1. Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.

  2. Einfügen eines neuen Moduls: Klicke mit der rechten Maustaste auf "VBAProject (DeineDatei.xlsm)" und wähle Einfügen > Modul.

  3. Code für den SVERWEIS: Kopiere den folgenden VBA-Code in das Modul:

    Sub sverweisMitVBA()
       Dim z As Long, lz As Long, s As Integer
       lz = Range("A65536").End(xlUp).Row
       If Range("A65536") <> "" Then lz = 65536
       On Error Resume Next
       For z = 1 To lz 'Zeilen
           For s = 2 To 12 'Spalten B bis M
               Cells(z, s).Value = WorksheetFunction.VLookup(Cells(z, 1).Value, Range("Testmatrix"), s, False)
               If Err.Number > 0 Then
                   Err.Clear
                   Cells(z, s) = "#NV!"
               End If
           Next s
       Next z
    End Sub
  4. Anpassen der Matrix: Stelle sicher, dass der Bereich "Testmatrix" in Deinem Excel-Dokument definiert ist.

  5. Ausführen des Makros: Schließe den VBA-Editor und führe das Makro über die Entwickler-Optionen oder mit ALT + F8 aus.

Jetzt sollte der SVERWEIS für die Werte in Spalte A in den Zellen B1 bis M1 korrekt umgesetzt werden.


Häufige Fehler und Lösungen

  • Fehlermeldung #NV!: Diese Meldung tritt auf, wenn der gesuchte Wert nicht gefunden wird. Stelle sicher, dass die Werte in der Matrix korrekt sind.
  • Fehler beim Ausführen des Makros: Überprüfe, ob die Matrix "Testmatrix" korrekt definiert ist und ob sie die gesuchten Werte enthält.
  • Macro-Sicherheitseinstellungen: Stelle sicher, dass Makros in Excel aktiviert sind, um den Code auszuführen.

Alternative Methoden

  1. XVERWEIS in VBA: Wenn Du die neueste Excel-Version verwendest, kannst Du den XVERWEIS verwenden, der flexibler ist. Der Code könnte so aussehen:

    Cells(z, s).Value = Application.WorksheetFunction.XLookup(Cells(z, 1).Value, Range("Suchbereich"), Range("Rückgabebereich"), "#NV!")
  2. Formel in Zelle schreiben: Anstatt VBA zu verwenden, kannst Du auch die SVERWEIS-Formel direkt in eine Zelle schreiben:

    =SVERWEIS(A1, Testmatrix, 2, FALSCH)

Praktische Beispiele

  • Beispiel 1: Angenommen, Du hast die Werte in Spalte A (A1:A10) und möchtest die entsprechenden Werte aus der Matrix in den Zellen B1 bis M10 einfügen.

  • Beispiel 2: Wenn Du den XVERWEIS verwendest, kannst Du diesen in Zelle B1 so umsetzen:

    Cells(z, s).Value = Application.WorksheetFunction.XLookup(Cells(z, 1).Value, Range("Testmatrix"), Range("Rückgabebereich"))

Diese Beispiele zeigen, wie Du den SVERWEIS in VBA umsetzen kannst.


Tipps für Profis

  • Fehlerbehandlung verbessern: Statt On Error Resume Next zu verwenden, könntest Du gezielte Fehlerbehandlungen implementieren, um genauere Fehlermeldungen zu erhalten.
  • VBA sverweis in zelle schreiben: Wenn Du Werte in bestimmten Zellen setzen möchtest, kannst Du dies gezielt tun, indem Du die Zellreferenzen direkt angibst.
  • Makro optimieren: Überlege, ob Du den Bereich dynamisch gestalten kannst, um die Performance zu verbessern, insbesondere bei großen Datenmengen.

FAQ: Häufige Fragen

1. Kann ich den SVERWEIS auch ohne VBA umsetzen?
Ja, Du kannst die SVERWEIS-Formel direkt in eine Zelle eingeben, wenn Du kein Makro verwenden möchtest.

2. Was ist der Unterschied zwischen SVERWEIS und XVERWEIS?
XVERWEIS ist flexibler und ermöglicht es, sowohl vertikale als auch horizontale Suchen durchzuführen. Zudem kann er Standardwerte zurückgeben, wenn kein Treffer gefunden wird.

3. Wie kann ich sicherstellen, dass meine Matrix immer aktuell ist?
Du kannst die Matrix dynamisch gestalten, indem Du benannte Bereiche oder Tabellen in Excel verwendest, die sich automatisch anpassen, wenn Du Daten hinzufügst oder entfernst.

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