Sverweis per VBA

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

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Sverweis per VBA

12.08.2005 16:25:19

Matthias
AW: Sverweis per VBA

12.08.2005 16:30:11

Thomas
AW: Sverweis per VBA

12.08.2005 16:42:12

Matthias
AW: Sverweis per VBA

15.08.2005 07:55:45

Thomas

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

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

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

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

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

Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor in Excel zu öffnen.
Einfügen eines neuen Moduls: Klicke mit der rechten Maustaste auf "VBAProject (DeineDatei.xlsm)" und wähle Einfügen > Modul.

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

Anpassen der Matrix: Stelle sicher, dass der Bereich "Testmatrix" in Deinem Excel-Dokument definiert ist.
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

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!")
```
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)

Forumthread: Sverweis per VBA

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Sverweis mit VBA umsetzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread