SVERWEIS über mehrere Blätter - mit UDF
17.03.2005 13:20:55
Boris
Hi Josef,
...ich hab kürzlich eine ähnliche Funktion für ZÄHLENWENN über mehrere Blätter geschrieben - und sie jetzt kurzerhand für den SVERWEIS umgebaut.
Option Explicit
Function SVERWEIS3D(Bezug As Variant, Suchkriterium As Variant, Spaltenindex As Integer) As Variant
Dim s As String 'Formelstring
Dim WsStart As String 'Name Erstes Blatt
Dim WsEnde As String 'Name Letztes Blatt
Dim iCount As Integer 'Zähler vom 1. bis zum letzten Blatt
Dim i As Integer 'Index des 1. Blattes
Dim j As Integer 'Index des letzten Blattes
Dim B As Boolean 'Prüfung, ob Mehrfachbezug vorhanden
Dim sBereich As String 'Bereich auslesen bei Mehrfachmarkierung
Const MB As String = "*:*!*,*" 'typische Syntax für Mehrfachbereich
s = Application.Caller.Formula 'Formel auslesen
'Prüfen, ob Mehrfachmarkierung vorliegt
B = s Like MB
If B Then 'Falls Mehrfachbezug vorhanden in der Form: Tabelle1:Tabelle3!A1:C100
'Formelstring innerhalb der Klammern auslesen und an s übergeben
s = Mid(s, InStr(1, s, "(") + 1, InStr(1, s, ")") - InStr(1, s, "(") - 1)
'Erstes Blatt auslesen
WsStart = Trim(Left(s, InStr(1, s, ":") - 1))
'Letztes Blatt auslesen
WsEnde = Trim(Mid(s, InStr(1, s, ":") + 1, InStr(1, s, "!") - InStr(1, s, ":") - 1))
'Zellbereich auslesen
sBereich = Trim(Mid(s, InStr(1, s, "!") + 1, InStr(1, s, ",") - InStr(1, s, "!") - 1))
'Index des 1. Blattes ermitteln
i = Worksheets(WsStart).Index
'Index des letzten Blattes ermitteln
j = Worksheets(WsEnde).Index
'Schleife über alle tangierten Blätter
For iCount = i To j
With WorksheetFunction
If .CountIf(Sheets(iCount).Range(sBereich).Columns(1), Suchkriterium) Then
SVERWEIS3D = .VLookup(Suchkriterium, Sheets(iCount).Range(sBereich), Spaltenindex, 0)
Exit Function
Else
SVERWEIS3D = "x" * 1
End If
End With
Next iCount
Else 'Falls kein Mehrfachbezug vorhanden: Einfacher SVERWEIS
SVERWEIS3D = WorksheetFunction.VLookup(Suchkriterium, Bezug, Spaltenindex, 0)
End If
End Function
Funktionssyntax:
=SVERWEIS3D(Bezug;Suchkriterium;Spaltenindex)
Den klassischen 4. Paramter des SVERWEIS (Bereich_Verweis) hab ich einfach mal weggelassen - es wird NUR auf exakte Übereinstimmung geprüft.
Für den Parameter Bezug kannst du hier auch eine Mehrfachmarkierung (erzeugt bei gedrückter Shift-Taste) einsetzen - also z.B.:
=SVERWEIS3D(Tabelle1:Tabelle3!B1:Z100;A1;2)
Gesucht wird der Wert aus A1 in der Matrix B1:Z100 in allen Blättern, die zwischen Tabelle1 und Tabelle3 liegen. Sobald ein Treffer vorliegt, wird das Ergebnis (hier aus der 2. Spalte) geliefert.
Natürlich kannst du diese eigene Funktion auch nur auf 1 Blatt anwenden - dann ist es nix anderes, als der normale SVERWEIS.
Wird nix gefunden, dann gibt es #WERT!.
Um die Funktion einzubinden:
VBA-Editor mit Alt+F11 öffnen, Einfügen - Modul - und dann in das rechte Fenster den gesamten Code reinkopieren.
Editor dann wieder schließen - und du kannst die Funktion wie beschrieben anwenden.
Grüße Boris