Im letzten Beitrag haben wir ja nur gezeigt, wie man festgestellt, dass es mehrere Treffer geben kann. Aber wie bekommt man nun angezeigt, welche?
Hierzu möchte ich nun beginnen, einen SVERWEIS selbst zu programmieren, was ja mit Visual Basic für Applications (VBA) sehr leicht möglich ist: den MySVERWEIS
Wie kann man den nun darstellen, welche Treffer es gibt, falls der SVERWEIS mehrere finden könnte? Am besten in Form einer Liste …
Beginnen wir mit einer Minimal-Variante eines SVERWEIS: nur einmal die Funktion, ohne Berücksichtigung von Performance, Komfort und mehrfachen Treffern.
Schnell per ALT-F11 den Visual Basic Editor gestartet und ein neues Modul in die PERSONAL.XLSB eingefügt. Nach meiner Gewohnheit: nun sofort einen Vernünftigen Namen fürs Modul vergeben: mdlFunktionen. Hier fügen Sie den folgenden Code ein:
Function mySVERWEISmini(Suchbegriff As Variant, Suchspalte As Range, Abstand As Integer)
‚ wie SVERWEIS, nur besser: kann auch nach links! Minimalversion, WORSICHT: nicht ganze Spalte als Suchbereich markieren!
For Each rngZelle In Suchspalte
If rngZelle = Suchbegriff Then mySVERWEISmini = rngZelle.Offset(0, Abstand)
Next rngZelle
End Function
Wenn Sie die Funktion nun verwenden wollen, kopieren Sie das Modul am Besten in die betreffende Datei. (Was nützt es den Kolleginnen und Kollegen, wenn Sie eine Funktion einsetzen, die mit Ihrem Notebook bzw. der dort gespeicherten PERSONAL.XLSB verbunden ist, wenn Sie auf Reisen oder im Urlaub sind??? Also: Kopie des Moduls in die Datei, wo Sie die Funktion einsetzen wollen!)
Funktionen werden aber nicht mit Makro-Ausführen eingesetzt, sondern wie eine normale Funktion eingegeben, entweder direkt getippt oder per Funktions-Assistent, als wäre die Funktion immer schon in EXCEL enthalten gewesen!
Sie sehen, einen SVERWEIS selbst herzustellen, ist recht einfach: 5 Zeilen Code reichen aus! Und die Funktionalität ist schon erweitert: wenn Sie bei Abstand negative Werte eingeben, geht der SVERWEIS_mini auch nach Links!
Falls Sie aber als Suchbereich die ganze Spalte markiert haben, durchsucht Excel immer 1 Mo. Zellen! Das bemerkt man an leicht verlangsamter Reaktionszeit. Auch ist das Ergebnis, falls kein Treffer gefunden wurde, noch recht zufällig gleich 0.
Nun mochten wir das Ganze etwas performanter und komfortabler gestalten: der SVERWEIS soll nach einem Treffer sofort abbrechend und nur untersuchen, wo Daten vorliegen, nicht mehr 1 Mio. Zeilen!
Jetzt geht’s los mit der Programmierung:
Function mySVERWEIS(Suchbegriff As Variant, Suchspalte As Range, Abstand As Integer)
‚ wie SVERWEIS, nur besser! Kann auch nach links!
‚ Parameter
‚ Suchbegriff: eben der
‚ Suchspalte: eben die. Wichtig: nur eine Spalte markieren
‚ Abstand: Abstand der Spalte, die als Ergebnis zurückgegeben wird.
‚ Negative Zahlen links, positive rechts.
mySVERWEIS = „#NV“ ‚ Vorbelegung, falls kein Treffer
For Each rngZelle In Intersect(Suchspalte, Suchspalte.Parent.UsedRange) ‚ Suche nur im benutzten Bereich!
If rngZelle = Suchbegriff Then
mySVERWEIS = rngZelle.Offset(0, Abstand)
Exit Function
End If
Next rngZelle
End Function
Diese Variante kann nun einen Treffer liefern. Interesse an allen Treffern? Der MySVERWEIS lässt sich beliebig anpassen: hier die Variante mit mehreren Treffern:
Function mySVERWEISmulti(Suchbegriff As Variant, Suchspalte As Range, Abstand As Integer)
‚ wie SVERWEIS, nur besser!
‚ Zeigt alle Treffer an!
‚ zuerst suchen wir alle Treffer
For Each rngZelle In Suchspalte
If rngZelle = Suchbegriff Then
mySVERWEISmulti = mySVERWEISmulti & rngZelle.Offset(0, Abstand) & „,“
‚Exit Function
End If
Next rngZelle
‚ nun werden alle Treffer aufgelistet
If Len(mySVERWEISmulti) > 0 Then
mySVERWEISmulti = Left(mySVERWEISmulti, Len(mySVERWEISmulti) – 1)
End If
End Function
Alle diese MySVERWEIS-Varianten finden Sie in der Beispieldatei…
Feedback erwünscht: Wir freuen uns, wenn Sie Ideen an uns senden, was der „normale“ SVERWEIS können sollte – wir nehmen da gerne als Inspiration für neue Varianten des MySVERWEIS…
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
3 Antworten
Warum ein Blatt selektieren um es zu ändern, außer das der Bildschirm anfängt zu „blinken“?
Public Sub KFZ_Alle()
On Error Resume Next
For Each Blatt In ActiveWorkbook.Sheets
Call KFZ(Blatt)
Next Blatt
MsgBox „Fertig!“
End Sub
Public Sub KFZ(Optional ByVal sh As Object)
If sh Is Nothing Then Set sh = ActiveSheet
With sh.PageSetup
.LeftHeader = „“
.CenterHeader = „&A“
.RightHeader = „“
.LeftFooter = „Peter Rühm“ & Chr(10) & „Pollmann & Rühm Training“
.CenterFooter = „Seite &P / &N“ & Chr(10) & „“
.RightFooter = „&D, &T“ & Chr(10) & „&Z&F“
End With
End Sub
Die Funktion sollte dimensioniert werden und ein flexibles Trennzeichen könnte auch gleich übergeben werden:
Public Function mySVERWEISmulti(Suchbegriff As Variant, Suchspalte As Range, Abstand As Integer, Optional Trennzeichen As String = „, „) As String
Dim v As String
For Each rngZelle In Suchspalte
If rngZelle = Suchbegriff Then
mySVERWEISmulti = mySVERWEISmulti & v & rngZelle.Offset(0, Abstand)
v = Trennzeichen
End If
Next rngZelle
End Function
Hallo,
danke dir für die Erstellung dieser Funktion. Diese erscheint mir als sehr hilfreich.
Ich weiß nicht in wie fern hier noch mit Support zu rechnen ist (es handelt sich ja doch um einen etwas älteren Eintrag), aber gäbe eine eine Variante, in der die Ergebnisse der Multi Funktion in untereinader liegenden Zellen ausgegegben werden, statt getrennt durch ein Komma in einer Zeile?
Ich habe verschiedenes erfolglos versucht.
Beste Grüße
Josephine