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: 73
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: 7141
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: 73
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$:-?
 

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 07:17

Moin moin Luc$:-?,

herzlichen Dank für die detaillierte Ausführung :) Der Hauptfehler zuvor war also das fehlende 3 Argument. Inwiefern ist folgende Schreibweise denn anders bzw. wie funktioniert diese anders? Dort existiert ja kein drittes Argument

Code: Alles auswählen
pstatus("ALLRAD!Q"&VERGLEICH($C21;ALLRAD!$A:$A)&":S"&VERGLEICH($C21;ALLRAD!$A:$A))


Habe mal in der Beispieldatei mein voriges Anliegen konkretisiert:

Mit der folgenden Formel findet er leider nicht die gewünschte Spalte:

Code: Alles auswählen
=Pstatus2_MB(INDEX(Tabelle5!A:D;VERGLEICH($B2;Tabelle5!A:A;0);VERGLEICH("11";Tabelle5!1:1;0)):INDEX(Tabelle5!A:D;VERGLEICH($B2;Tabelle5!A:A;0);VERGLEICH("13";Tabelle5!1:1;0)))


Für die Suche der Spalte sollte doch die gleiche Systematik funktionieren oder?


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

Re: VBA: Index VGL in UDF

Beitragvon DerHoepp » 22. Mai 2018, 09:10

Moin,

Deine erste Formel macht auf den ersten Blick ohne die Verwendung von Indirekt keinen Sinn.
Zur zweiten Formel bleibt das Raten:
"11" ist nicht 11.
Ansonsten empfehle ich dir, die Einzelteile der Formel zunächst einmal auszuprobieren. Du kannst zunächst wunderbar mit mehreren Spalten arbeiten um zunächst einmal die richtigen Formelteile für deine Vergleichsformeln herauszufinden. Dabei hilft dir sicherlich auch die Formelauswertung weiter. Erst, wenn du Index und Vergleich durchschaut hast, würde ich das ganze an deine UDF übergeben.

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

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 09:30

Ohman...Ohne Anführungszeichen klappts prima, danke für den Hinweis.

Kannte das bei Excel bisher bei Suchen nur mit den Anführungszeichen...in diesem Fall nur dank des "Vergleichstyps" nicht von Nöten?
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 73
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon DerHoepp » 22. Mai 2018, 09:54

Hallo don,

in diesem Fall nur dank des "Vergleichstyps" nicht von Nöten?

Nein. Mit "11" suchst du nach der Zeichenkette einseins, mit 11 nach der Zahl Elf. Text und Zahlen sind unterschiedlich.

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

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 10:14

Ahh okay interessant, vielen Dank ;)

Habe noch kurz was zum Code selbst. Falls ich damit eher einen neuen Thread starten soll, einfach Bescheid geben:

Viele benutzen nicht immer die gleiche Farbe für grün, rot oder weiß. Ich möchte nun definieren, dass nicht nur "2" als weiß registriert wird. Mit "Or" funktioniert das leider nicht so ganz...Nehme ich z.B. ein helles Grau mit "15" hinzu:

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 [b]Or 15[/b] 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 [b]Or 15 [/b]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 = 6 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


Bekomme dann für alle Konstellationen ein "Angelegt" als Status?

Muss ich für jede Farbe ein eigenes "If" erstellen?
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 73
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon DerHoepp » 22. Mai 2018, 10:18

Hallo,

Muss ich für jede Farbe ein eigenes "If" erstellen?

Nein, du musst nur jeden Teil einzeln auswertbar machen:
Code: Alles auswählen
If rngZelle.Interior.ColorIndex = 2 Or rngZelle.Interior.ColorIndex =15 Then


Dass es nicht sonderlich vorteilhaft ist, in einer Tabellenkalkulation Farben als Eingabemedium zu verwenden hat man dir wahrscheinlich schon gesagt, oder? Besser wären Zellinhalte.

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

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 10:23

Ahh okay...

Ja schon öfter, aber ich erstelle sie nicht, sondern werte diese für meine Thesis aus...daher etwas machtlos ;)

BTW:

Da Interior. Color ja präziser ist als der Colorindex... Kann das ohne Weiteres einfach im Code ersetzen? Natürlich mit den entsprechenden umgewandelten RGB Werten...
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 73
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon Nicolaus » 22. Mai 2018, 11:18

Hi,

oder du verwendest Select Case, da kannst du mehrere mit Komma getrennt angeben:
Code: Alles auswählen
   For Each rngZelle In rngSuchbereich
      ' Wenn min. eines Weiß ist aber nicht alle, dann "GEHT NICHT AN"
      Select Case rngZelle.Interior.ColorIndex
        Case 2, 15
         Pstatus2_MB = "GEHT NICHT AN"
         Exit Function
      End Select
   Next rngZelle
Gruß
Nic
die Forenhelfer freuen sich über eine Antwort
Benutzeravatar
Nicolaus
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4572
Registriert: 02. Feb 2010, 15:52
Wohnort: Rhein Main Gebiet

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 11:53

@DerHoepp @Nicolaus

Besten Dank, funktioniert beides sehr gut und hilft mir weiter :) Das mit dem Wechsel auf "Interior.Color" probier ich selbst dann mal und meld mich, falls es nicht klappt :)
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 73
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon teflon_don » 22. Mai 2018, 14:35

Gibt´s eigentlich ne Möglichkeit, die eigene Ausgabe des Makros innerhalb des Makros selbst zu nutzen?

Habe ja im letzten Abschnitt definiert, dass wenn neue Farben ausserhalb der definierten auftauchen, das Makro ein "N.N. GEREGELT" liefert:

Code: Alles auswählen
   ' Wenn bisher nichts zugetroffen ist, dann müssen alle Zellen grün sein
   Pstatus3 = "N.N. GEREGELT"


Kann ich diesen Status vorher dann wieder nutzen, um zu sagen, dass der Gesamtstatus dann "N.N. GEREGELT" lautet?
Also iwie sinngemäß:
Code: Alles auswählen
If Pstatus3="N.N. GEREGELT" Then Pstatus3="N.N. GEREGE´LT"
:D

Denn momentan liefert er mir, aufgrund der Priorisierung, bei einer Rangeabfrage die ein "N.N..." als Einzelstatus beinhaltet, einen Gesamtstatus von "GEHT NICHT AN".

Alternativ könnte ich vorher ja einfach festlegen, dass der Gesamtstatus "N.N. GEREGELT" dann eintreffen soll, wenn <> der bisher verwendeten Farben, was aber aufwändiger wäre.

Gibt´s da ne intelligentere Lösung für? Meine Idee von oben wäre ja sinngemäß ein Zirkelbezug und so nicht machbar oder? :)
teflon_don
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 73
Registriert: 24. Nov 2016, 08:46

Re: VBA: Index VGL in UDF

Beitragvon Flotter Feger Gast » 22. Mai 2018, 19:08

Hallöchen,

bin jetzt doch ein wenig verwirrt ... du baust an einer Abschlußarbeit und wertest dafür farbige Excelzellen aus :?:
Gibt´s da ne intelligentere Lösung für?
Du meinst, außer Werte zu verwenden und keine Farben ? :wink:

Schon mal daran gedacht, eine erweiterbare Legende für deine "Werte" zu erstellen ?
Immer, wenn du eine neue Farbe hinzubekommst ... man klingt das schräg :cry: ... legst du in einer Sub einen neuen Eintrag in deiner 'Legende' an.
Dann kann dein Programm beim nächsten Durchlauf in der 'Legende' nachsehen, ob die Farbe schon abgelegt und betitelt wurde.

Nur mal so eine Idee ...

VG Sabina
Flotter Feger Gast
 

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: DerFlo123, Hajo_Zi, KaySay, knobbi38, LM6, MisterBurns, sebbas86, timo1010 und 9 Gäste