Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
232to236
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
232to236
232to236
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Formel in VBA übersetzen

Formel in VBA übersetzen
16.03.2003 14:17:28
Max
Hallo!

Folgendes Problem:

Ich habe für jeden Monat eine Anwesenheitsliste nach folgendem Schema:

anwesend x 2 0 0 Urlaub u 2 0 0
Überstunden g 1 3 3
krank k 0 0 0
frei f 0 2 2

Sa So Mo
Schicht Skill Team Name 1 2 3
spät 1 FCB Maier x f f
spät 1 FCB Müller x f f
früh 2 FCB Lerby u g g
früh 2 FCB Ballack u g g
früh 3 FCB Matth. g g g

Der Bereich oben zählt zusammen, wie viele Mitarbeiter pro Tag anwesend sind, krank sind, Urlaub haben, etx. Konkret werden einfach die entsprechenden Kürzel (x= anwesend, k= krank, u = urlaub,...) zusammengezählt. (Leider stimmt hier die Formatierung nicht. Die Wochentage stehen normal über den Datumswerten (1,2,3...), usw.)

Die Mitarbeiter arbeiten in Früh- und Spätschicht und haben verschiedene Skills. Die Kürzel links neben den Namen kennzeichnen die Schicht und den Skill.

Das Zusammenzählen ließe sich prime mit z. B. "=ZÄHLENWENN f20:f50;"u")" erledigen.

Allerdings möchte ich, dass die Zählung in Abhängigkeit eines Autfilters gemacht wird. Zum Beispiel möchte ich nur die Spätschicht betrachten. Also filtere ich nach Spätschicht.

Die ZÄHLENWENN-Funktion versagt hier, weil sie den Autofilter nicht berücksichtigt.

Mit der TEILERGEBNIS-Funktion wäre es möglich, den Autofilter zu berücksichtigen. Aber TEILERGEBNIS zählt einfach stur alle Einträge im angegebenen Bereich und unterscheidet nicht nach u, k, x, etc.

Eigentlich bräuchte ich eine Kombination aus ZÄHLENWENN und TEILERGEBNIS.

BORIS hat mir diesbezüglich schon weitergeholfen und folgende Formel entwickelt:

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE($14:$60)))*(F$14:F$60="u"))

Was macht die Formel? Hier SINNGEMÄSS die Erklärung von Boris:

"Vereinfacht gesagt, werden die sichtbaren Zeilen mit den sichtbaren Zeilen in Spalte F verglichen. Steht in Spalte F ein "u", dann ergibt (F20:F60="u") für die jeweilige Zeile den Wert WAHR - und WAHR bedeutet - auch vereinfacht gesagt - 1.
Wenn also die mit Teilergebnis ermittelte Zeile (in Kombination mit INDIREKT) sichtbar ist UND in Spalte F ein "u" steht, dann hat man 2 WAHR-Werte, und das ist 1*1, also 1.
Und die Summe der Einsen (daher SUMMENPRODUKT) gibt dir die Anzahl der "u" aus der gefilterten Liste wieder."

So weit so schön. Es funktioniert auch perfekt. Ich habe aber noch mehr vor:

BEISPIELSWEISE soll, wenn eine bestimmte Anzahl an Urlauben überschritten wird, die Zelle rot markiert werden (oder ähnliches).

Die Abhängidkeiten sind recht komplex. Bedingte Formatierung bringt mich NICHT weiter. Fakt ist, ICH BRAUCHE DEFINITIV VBA DAZU.

Der erste Schritt wäre, die Formel "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE($14:$60)))*(F$14:F$60="u"))" in VBA zu übersetzen. Und soweit bin ich schon:

Ich übergebe per Funktionsaufruf der selbstgeschriebenen Funktion Urlaub ein Range-Objekt (z. B. =urlaub(f20:f60)). Die Funktion selbst sieht in etwa so aus:

Function urlaub(r As Range)
urlaub = Application.WorksheetFunction.SumProduct(Subtotal(3, INDIRECT("F" & r.Row) * r = "u"))
End Function

Leider meckert Excel, weil es INDIRECT nicht kennt. Es gibt leider keine VBA-Ensprechung für INDIREKT(Verweis;A1). (Obwohl der Makrorecorder Indirekt in INDIRECT übersetzt hat)

Ein Lösungsweg wäre, eine eigen Funktion INDIRECT zu schreiben. Aber wie geht das? Bestimmt gibt es auch noch andere Möglichkeiten.

Es wäre schon, wenn mir jemand auf die Sprünge helfen könnte!!!

Viele Grüße,

Max

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Re: Formel in VBA übersetzen
16.03.2003 19:52:38
gordon

Hi Max,

mal aus dem Bauch: Du müsstest entweder auf die Autofilter-Fkt. in VBA verzichten können und die Zellen per Vergleich selbst ermitteln, oder die entireRow.hidden-Eigenschaft der row des Range-Objektes auf true/false abprüfen - Die hidden=false zeilen dann summieren.

Gruß
gordon

Re: Formel in VBA übersetzen
16.03.2003 20:25:17
Max

Hi!

Vielen Dank für die Tipps. Jetzt bin ich das Problem aber schon los. Ich bin drauf gekommen, dass ich mir die Indirekt-Funktion sparen kann, indem ich einfach auf eine feste Zelle verweise, die immer einen Wert hat (z. B. J14). Konkret sieht es dann so aus:

"=SUMMENPRODUKT(TEILERGEBNIS(3;(J13))*(J14:J60="g"))"

Die Übersetzung in VBA ist mir leider noch nicht geglückt. Hier bin ich auf das nächste Problem gestoßen. Ich glaube VBA interpretiert den * anders als Excel innerhalb einer Formel. Auf jeden Fall kriegen ich kein Ergebnis (#Wert). So sieht die VBA-Umsetzung momentan aus:

urlaub = Application.WorksheetFunction.SumProduct(Application.WorksheetFunction.Subtotal(3, Range("f13")) * (Range("f14:f60") = "u"))

Wenn ich den letzten Teil ab dem * weglasse, dann macht die Funktion genau das, was die entsprechende Excel-Funktion auch macht.

Wenn dir aus dem Bauch raus noch was einfällt wäre ich dir dankbar.

Grüße,

Max



Anzeige
Re: Formel in VBA übersetzen
16.03.2003 20:29:34
Max

Hallo noch mal!

Stimmt wohl nicht so ganz, was ich grade geschrieben habe. Das Problem mit Indirekt hat sich doch nicht erledigt. Meine Lösung berücksichtigt keinen Filter. Ich glaub ich sollte es für heute lassen. Es ist heut einer jener Tage....

Grüße,

Max

307 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige