Pimp my SVERWEIS: SVERWEIS mit mehreren Treffern = MySVERWEIS!

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…

mysverweis

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…

 

2 Gedanken zu „Pimp my SVERWEIS: SVERWEIS mit mehreren Treffern = MySVERWEIS!

  1. Hans-Peter

    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

    Liken

    Antwort
  2. Hans-Peter

    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

    Liken

    Antwort

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s