Superverweis

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

'Suchrichtung ZeilenOffset = Bereich.Columns.Count >= Bereich.Rows.Count SpaltenOffset = Not ZeilenOffset 'eigentliche Suche For Each Zelle In Bereich.Cells If Zelle.Value = Suchbegriff Then SuperVerweis = Zelle.Offset(-ZeilenOffset * Wiedergabe, -SpaltenOffset * Wiedergabe) Exit Function End If Next Zelle

Function Superverweis(ArtVerweis As Long, Kriterium As String, oMatrix As Range, oIndex As Long, _ BereichVerweis As Long) '1 = sverweis '2 = verweis '3 = Sverweis links (Matrix = nur eine Spalte) '4 = wverweis '5 = wverweis oben (Matrix = nur eine Zeile) Select Case ArtVerweis Case 1 Superverweis = WorksheetFunction.VLookup(Kriterium, oMatrix, oIndex, BereichVerweis) Case 2 Superverweis = WorksheetFunction.Lookup(Kriterium, oMatrix) Case 3 Superverweis = oMatrix.Find(Kriterium).Offset(0, oIndex) Case 4 Superverweis = WorksheetFunction.HLookup(Kriterium, oMatrix, oIndex, BereichVerweis) Case 5 Superverweis = oMatrix.Find(Kriterium).Offset(oIndex, 0) Case Else Superverweis = "NA" End Select End Function

als MatrixFml eingibst (das ist bei Datenfeldern fast immer erforderlich), Jack,
kommt auch C raus, bei deiner UDF SuperVerweis aber nicht, was daran liegen mag, dass du Datenfelder ausschließt wie das bspw auch die xlFktt RANG, SUMMEWENN u.Ä. im Ggsatz zu uralten xlFktt wie SUMME, SUMMENPRODUKT, ANZAHL, INDEX und eben die VerweisFktt.
Mit ""&bereich wird mit diesem FktsArgument erst eine Berechnung ausgeführt (bei Zahlen hätte ich -- genommen), wodurch die Werte von bereich von ihrem Standort getrennt wdn, der ZellBezug wird also aufgehoben. Die Werte wdn der Fkt dann also nicht mehr As Range (also als ObjektReferenz), sondern als reine WerteFolge, die man gemeinhin Datenfeld nennt, übergeben. Dazu muss das Argument aber As Variant (oder ohne DatenTypFestlegung) deklariert sein, sonst wird es nicht akzeptiert und es wird sofort ein Fehler generiert (ohne die Fkt erst abzuarbeiten, dafür sorgt schon die XlSteuerung, die das abprüft).
Wenn du bspw ein Argument einer Fkt As String deklarierst, wird alles als Argument akzeptiert, was entweder ein Text ist oder ggf in einen solchen umgewandelt wdn kann (kein Fehlerwert!), auch Bezüge auf eine EinzelZelle mit einem solchen Wert. Wird aber ein Zell(Bereichs)Bezug verlangt, wird hier auch nur eine ObjektReferenz auf einen ZellBereich akzeptiert, egal, was der Bereich enthält.
Ein As Variant deklariertes Argument kann dagg alles enthalten, sowohl ZellBezüge als auch direkt angegebene Einzelwerte oder gar Datenfelder wie sie aus der (idR zuerst erfolgenden) Berechnung von Ausdrücken als Argument resultieren.
Manche XlFktt sind auf die Angabe von Bereichsbezügen und Datenfeldern ausgerichtet. Diese kommen dann idR ohne Kennzeichnung als MatrixFml aus, es sei denn, eine Fkt in einem als Ausdruck notierten Argument verlangt das, um auch alle Werte und nicht nur den 1. des resultierenden Datenfeldes zurückzugeben wie es zB bei WENN der Fall ist. Willst du so etwas in einer eigenen UDF verwenden, musst du das Argument stets mit IsArray(argument) prüfen, um dann darauf eine zyklische Verarbeitung all seiner Werte aufzusetzen. Das gilt besonders auch bei Functions, die in der bzw zur ArgumentÜbergabe ein ParamArray verwenden. Das ist grdsätzl Optional Variant und seine einzelnen Elemente können folglich alles enthalten, auch Datenfelder, nichts oder fehlen (fehlen alle ergibt UBound() -1, LBound() aber stets 0!).
Aber auch bei BereichsBezügen gibt's noch einige Besonderheiten, auf die ich hier mal kurz eingehen will:
Bei solchen Bezügen können zusätzlich 3 OperationsZeichen verwendet wdn, : für vonbis-Bezüge (man kann auch .. schreiben, was sofort durch : ersetzt wird), ; für Aufzählungen (eigentl stets das lokale ListenTrennZeichen, im US-Original ,) und LeerZeichen (blank⇔Zeichen32) für SchnittmengenOperationen (vbFkt Intersect).
Eine Aufzählung muss dabei stets geklammert wdn. Allerdings können nicht alle XlFktt damit etwas anfangen, denn es entsteht dadurch ein unzusammenhängender Bereich, eine sog MehrfachAuswahl. INDEX kann das weitgehend (nicht alles ist auch zusammenhängend darstellbar), dafür steht das optionale 4.Argument, mit dem ggf eine Auswahl getroffen wdn kann. Demggüber verträgt INDIREKT nur Einzel- und BreichsAdressen, keine anderen Operationen. INDEX ist wohl auch die einzige Fkt, die mit einer BereichsAngabe wie (C34:D35;C37:D38) D35:E37 klar kommt. INDEX scheint hier anders zu ticken als normalerweise in Xl üblich, weil man keine Chance hat, das mit einer UDF nachzugestalten, da Xl diesen Ausdruck vorberechnet und der UDF dann nur eine TeilReferenz übergibt (das Ergebnis der Operation wäre eigentlich (D35;D37), die UDF erhält aber nur D35). Man müsste in diesen Fällen mit einer UDF auf einen BezugsText zurückgreifen (bzw auf den Inhalt der FmlStandortZelle, also die Fml selbst in der Fml als Text behandeln und dieses Argument dann als Text isolieren).
Damit weißt du nun, was mich ggw im Rahmen der Pgmierung einer universellen UDF zur Behandlung beliebiger Datenfelder (zB aus vbFkt Split: numerisches Element → echte Zahl, Text bleibt Text, FehlerWert/Text → FWert oder ErsatzWert, Text Wahr/Falsch/True/False → Wahrheitswert, Leer → LeerString oder -0 usw) inkl Indirekt-Option umtreibt.
Gruß, Luc :-?
Besser informiert mit …

…UDF-Deklarationsunterschied findest du 2 Threads tiefer in dem von DirkR, Jack;
was hier mit VERKETTENWENN möglich ist, kann die andere UDF wg ihrer ArgumentDeklaration nicht leisten.
Ich deklariere ein Argument nur dann As Range, wenn die UDF definitiv andere ZellEigenschaften als nur .Value ermitteln/verwenden soll. Soll bei (impliziter) Deklaration As Variant zwischen Bereichsbezügen und Datenfeldern unterschieden wdn, frage ich den evtl BereichsBezug mit TypeName(bezug) = "Range" ab. Anschließend wandle ich häufig den Bereichsbezug mit (doppeltem) WorksheetFunction.Transpose in ein Datenfeld um, um in der Folge beide Varianten gleich behandeln und mit den L-/UBounds arbeiten zu können. Manchmal ist das auch unpraktisch, zB bei Umwandlung in Datenfelder, die aus einem Vektor, dessen Elemente ebenfalls Vektoren sind, bestehen. Hier mache ich das im BereichsFall gleich, bei bereits übergebenen Datenfeldern dann mit WorksheetFunction.Index. Das hat aber Grenzen in der maximalen 2Dimensionalität, n-dimensionale Tensoren lassen sich nicht mit INDEX behandeln. Da muss man dann einen anderen Weg wählen.
Das alles gehört natürlich nicht mehr unbedingt zum Ausgangsproblem, enthält aber nützliche Hinweise, die du bei tieferem Eindringen in die Problematik auch erkennen wirst.
Luc :-?

Beliebteste Forumthreads (12 Monate)

26
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Superverweis

26 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

26
Beiträge zum Forumthread

Beiträge zu diesem Forumthread