VBA: Index VGL in UDF

Moderator: ModerationP

VBA: Index VGL in UDF

Beitragvon teflon_don » 18. Mai 2018, 10:32

Moin moin,

hoffe ich hab den Betreff richtig erfasst:

Habe hier von MB folgenden Code bekommen gehabt zur Abfrage und Auswertung eines Range mit Farben:

Code: Alles auswählen
Public Function Pstatus2_MB(rngSuchbereich As Range)
   Dim rngZelle As Range
   
   Application.Volatile

   ' Wenn in der Range alles Weiß ist, dann ists "ANGELEGT"
   If rngSuchbereich.Interior.ColorIndex = 2 Then
      Pstatus2_MB = "ANGELEGT"
      Exit Function
   End If

   ' Je Farbe alle Zellen durchlaufen
   ' Prüfung weiss
   For Each rngZelle In rngSuchbereich
      ' Wenn min. eines Weiß ist aber nicht alle, dann "GEHT NICHT AN"
      If rngZelle.Interior.ColorIndex = 2 Then
         Pstatus2_MB = "GEHT NICHT AN"
         Exit Function
      End If
   Next rngZelle
   
   ' Prüfung rot
   For Each rngZelle In rngSuchbereich
      If rngZelle.Interior.ColorIndex = 3 Then
         Pstatus2_MB = "GEHT NICHT"
         Exit Function
      End If
   Next rngZelle

   ' Prüfung gelb
   For Each rngZelle In rngSuchbereich
      If rngZelle.Interior.ColorIndex = 44 Then
         Pstatus2_MB = "GEHT Z.T."
         Exit Function
      End If
   Next rngZelle
   
   ' Wenn bisher nichts zugetroffen ist, dann müssen alle Zellen grün sein
   Pstatus2_MB = "GEHT"
End Function


In der Zelle schreibe ich =Pstatus2_MB(A1:C1). Möchte diese Abfrage aber mit einem Sverweis verknüpfen (siehe Reiter "Tabelle2" in Beispieldatei). Dies wollte ich per Index VGL machen:

=Pstatus2_MB(INDEX(Tabelle5!B:E;VERGLEICH($B2;Tabelle5!B:B);Tabelle5!C:C):INDEX(Tabelle5!B:E;VERGLEICH($B2;Tabelle5!B:B);Tabelle5!E:E))


Bekomme da leider nur ein #WERT Fehler, dessen Ursprung mir sich nicht erschließt. Jemand ne Idee? :)
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 66
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon DerHoepp » 18. Mai 2018, 11:54

Moin Don,

dein Index ist etwas merkwürdig aufgebaut. Normalerweise würde man sagen:
Code: Alles auswählen
=INDEX(MATRIX,Zeilennummer,Spaltennummer)

Dein Aufbau ist momentan:
Code: Alles auswählen
=INDEX(Matrix,Vergleichsformel,SpaltenZellBereich)

Wenn du also statt auf Spalte B auf die Spaltennummer 2 und statt Spalte D auf Spaltennummer 4 referenzierst, kommst du schon einen schritt weiter:
Code: Alles auswählen
=Pstatus2_MB(INDEX(Tabelle5!A:D;VERGLEICH($B2;Tabelle5!A:A);2):INDEX(Tabelle5!A:D;VERGLEICH($B2;Tabelle5!A:A);4))


Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6446
Registriert: 14. Mai 2013, 11:08

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 18. Mai 2018, 12:26

Hey DerHoepp,

Prima, hat hingehauen :)

Funktioniert das mit dieser Schreibweise auch? Verstehe nur nicht so ganz, wieso er es mit meiner Schreibweise nicht fand. Habe eine ähnliche Formel für die Summation von Werten, mit der es eigentlich immer ging.
Code: Alles auswählen
=SUMME(INDEX('Blatt1'!$A:$V;VERGLEICH($A6;Blatt1!$A:$A;0);VERGLEICH("I01/2018";'Blatt1'!$3:$3;0)):INDEX('Blatt1'!$A:$V;VERGLEICH($A6;'Blatt1'!A:A;0);VERWEIS(9;1/('Blatt1'!$A$3:$V$3="I03/2018");SPALTE('Blatt1'!$3:$3))))


Oder kann man VBA Funktionen nicht in gleichem Maße wie die normalen Formeln behandeln? :)

PS: Wenn ich in der Suchtabelle per Vergleich spezifische Spalten suchen möchte (z.B. anhand der Zeilen 1:1 über der Tabelle), bekomme ich auch den #WERT Fehler, woran liegt das? :)

=Pstatus2_MB(INDEX(Tabelle5!A:D;VERGLEICH($B3;Tabelle5!A:A);VERGLEICH("W";Tabelle5!1:1)):INDEX(Tabelle5!A:D;VERGLEICH($B3;Tabelle5!A:A);VERGLEICH("d";Tabelle5!1:1)))


Suchtabelle wurde natürlich um die Einträge w und d in Zeile 1 erweitert ;)

Besten Dank schon mal!!
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 66
Registriert: 24. Nov 2016, 08:46

INDEX-Fehler

Beitragvon Luc$:-? » 18. Mai 2018, 17:06

Den von derHöpp genannten Fehler hattest du ja in der SummenFml nicht gemacht, Don;
VERGLEICH liefert eine Zahl, nämlich die Zeile des angegebenen Bereichs ab seiner 1.Zeile gezählt. Genau die benötigt INDEX auch. Für die Spalte benötigt INDEX ebenfalls eine Zahl, nämlich die Nr der Spalte ab der 1.Spalte des Bereichs gezählt und nicht einen ganzen Bereich mit dessen Zahlwerten, die so als Argument verwendet wdn, was zum Fehler führt.
Das Ganze hat weder etwas mit der Xl-Fkt SVERWEIS, die hierbei nicht brauchbar wäre, noch mit der UDF zu tun, denn die verlangt einen BereichsBezug, den INDEX so ja auch liefern würde, wenn du diese Xl-Fkt nicht falsch argumentiert hättest. Zwischen UDFs und Xl-Fktt besteht prinzipiell kein BehandlungsUnterschied in ZellFmln, der von ihrer Pgmmierung unabhängig wäre.
Außerdem hast du in VERGLEICH das 3.Argument nicht angegeben, was aufsteigende Sortierung der VglsWerte voraussetzt. Anders wäre die Bildung eines zusammenhängenden TeilBereichs aber auch kaum möglich.
Gruß, Luc :-?
Luc$:-?
 


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: ec86 und 20 Gäste