SVERWEIS mit 3D-Bezug
24.09.2007 21:27:00
{Boris}
Hi,
ich hab da seinerzeit mal ne eigene Funktion für geschrieben:
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
End If
End With
Next iCount
SVERWEIS3D = "x" * 1
Else 'Falls kein Mehrfachbezug vorhanden: Einfacher SVERWEIS
SVERWEIS3D = WorksheetFunction.VLookup(Suchkriterium, Bezug, Spaltenindex, 0)
End If
End Function
Die Syntax lautet: =SVERWEIS3D(Bezug;Suchkriterium;Spaltenindex)
Also z.B.: =SVERWEIS3D(Tabelle2:Tabelle7!A1:B10;A1;2)
Die Blattnamen dürfen nur keine Leerzeichen enthalten.
Grüße Boris