Office: VBA- Zellen mit 2 Bedingungen Zählen (Farbe und Text)

Hallo zusammen,

Nach mehreren Tagen Recherche und diversen Codes ausprobieren und anpassen bin ich echt am Ende und suche hier im Forum Hilfe.

Zu meinem Problem:
- Ich möchte eine Spalte nach mehreren Werten abfragen und diese zusammen zählen
- daraus soll ein Ergebnis ausgegeben werden

Mein "Format" der Tabelle:

Bitte nicht gleich mit Steinen werfen, ich hab das Ding auch nur geerbt.

Es ist ein Anwesenheitskalender für 4 Schichten. Aufbau wie folgt:
Spalten A - E enthalten die Kollegen (Abteilung "A", Schichtfarbe "B", Schichtgruppe "C", Vorname "D", Nachname "E")
Ab Spalte F ist ein Jahreskalender angelegt (ist schon grausam genug).

Beispiel Monat Januar:
Reiher 1, Spalte G - AK sind verbunden und enthalten den Monatsnamen
Reihe 2 enthält die Kalenderwochen (ebenfalss teilweise verbundene Zellen)
Reihe 3 enthält die Ferien (nur kosmetik)
Reihe 4 ist für Schulungen deklariert (auch nur kosmetik)
Reihe 5 enthält die Wochentage abgekürzt (Mo, Di usw.)
Reihe 6 enthält den Tag als Datum (1, 2, 3 usw.)

Reihe 7 bis Reihe X sind die Schichten aufgelistet in Form von Buchstabe und Hintergrundfarbe (Beispiel: F mit grünen BG, steht für Frühschicht)

Nun zu meinem Problem:
Ich ermittle mittels Find den Tag, der abgefragt werden soll (ich hole mir ein Datum, setze den Monat und den Tag als Zahl und vergleiche die Tabelle). Daraus ermittle ich die Spalte die abgefragt werden soll (countIf). Mein Ergebnis gebe ich dann in eine MsgBox oder sonst was ab.
Ich kann diese Abfrage problemlos mit dem "Inhalt" der Zellen machen (F für Frühschicht, S für Spätschicht und N für Nachtschicht) und bekomme auch eine plausible Antwort.
Jetzt möchte ich das ganze noch an die Farben fest machen (die Zellen für "F" , "S" und "N" haben für sich immer die gleiche Hintergrundfarbe). Die Überprüfung soll deswegen erfolgen, weil wenn die Zelle eine andere Farbe als deklariert hat nicht gezählt werden soll.

Wenn die Zelle "F" nicht grün ist, ist der betroffene Mitarbeiter nicht anwesend (Kennzeichnung einer anderen Farbe als Grün für Abwesend).

Wie schon erwähnt, mit der Find und CountIf Methode bekomme ich den Inhalt der Werte zusammen, aber die Abhängigkeit zur Hintergrundfarbe fehlt mir noch. Mittels CountIfs habe ich es bereits versucht, aber das Arg2 kann ich nicht so recht deklarieren (müsste meines Wissens der Typ "Byte" sein um die Farbe in der Zelle zu ermitteln; frisst mir die CountIFs Anweisung nicht).

Ich habe wirklich schon sehr lange dran gearbeitet und bin echt am Ende.

Nochmal kurz zusammengefasst:
- Ich soll Zellen nach Text und Hintergrundfarbe zusammen zählen und Ausgeben
- Es gibt insgesamt 6 Kriterien, die zusammen gezählt werden sollen (2 pro Schicht, also 2 für "F", 2 für "S" und 2 für "N")


Ich hoffe ich konnte mich einigermaßen verständlich ausdrücken. Code oder sonstiges darf ich nicht posten, weil es mehr oder minder Firmeneigentum ist. Beispiel Codes werde ich bei Bedarf zur Verfügung stellen (also meinen umgebauten Code ohne die korrekten Bezüge was Tabellen usw. betrifft.)


regards
Klaus a.k.a. Streetdigger2

 

Moinsens!

Hmmm... Also... Du weißt, in welcher Spalte (nehmen wir mal "x" als Variable) Du suchen willst. du weißt auch, daß du ab Zeile 7 (inklusive, abwärts) suchen willst, müßtest jetzt noch wissen, bis zu welcher Zeile (nehmen wir mal "letztezeile" als Variable dafür). Damit läßt sich schon mal eine Schleife mit einer If-Abfrage basteln:

Code:

for y=7 to letztezeile
    ' if s1.cells(y,x)=...
next y

Da Du was zählen willst, brauchst Du einen Zähler, der vorher auf 0 initialisiert wird. Außerdem muß ja noch "letztezeile" herausgefunden werden:

Code:

letztezeile=s1.cells(s1.rows.count,x).end(xlup).row
zf1=0 : zf2=0
zs1=0 : zs2=0
zn1=0 : zn2=0

(Alternativ könnte man hier auch mit einem Array oder sogar besser noch mit einem Struktur-Typ arbeiten...)

Jetzt die Bedingung(en), bei denen die Zähler hochgezählt werden... Hmmm... Da bin ich mir nicht ganz sicher, ob ich aus Deiner Beschreibung schlau werde... Also... Wenn in der Zelle ein "F" (oder "S" oder "N") steht _und_ die Zelle grün eingefärbt ist, dann wird gezählt. Ist die Zelle nicht grün, wird nicht gezählt, egal was drin steht. Richtig? Und woher kommt dann "2 pro Schicht"? Stehen die nebeneinander in getrennten Zellen? Davon gehe ich jetzt mal aus, also daß das 2. Kriterium in Spalte "x+1" zu finden ist:

Code:

if s1.cells(y,x).interior.color=gruen then
    if s1.cells(y,x)="F" then zf1=zf1+1
    if s1.cells(y,x)="S" then zs1=zs1+1
    if s1.cells(y,x)="N" then zn1=zn1+1
endif
if s1.cells(y,x+1).interior.color=gruen then
    if s1.cells(y,x+1)="F" then zf2=zf2+1
    if s1.cells(y,x+1)="S" then zs2=zs2+1
    if s1.cells(y,x+1)="N" then zn2=zn2+1
endif

Es wird also zunächst geprüft, ob die Zelle grün ist, und wenn ja, dann wird eben ggf. einer der 6 Zähler hochgezählt. Jetzt wäre nur noch vorher festzulegen, was "gruen'" ist. Dazu kannst Du Dir den RGB-Wert zusammensetzen, z.B.:

Code:

gruen=rgb(0,255,0)

Jetzt noch ein paar Deklarationen drumherum und es sollte so in der Art aussehen:

Code:

dim s1 as worksheet
dim zf1 as integer
dim zf2 as integer
dim zs1 as integer
dim zs2 as integer
dim zn1 as integer
dim zn2 as integer
dim letztezeile as long
dim x as integer
dim y as long
dim gruen as long

set s1=activesheet

gruen=rgb(0,255,0)
letztezeile=s1.cells(s1.rows.count,x).end(xlup).row

zf1=0 : zf2=0
zs1=0 : zs2=0
zn1=0 : zn2=0

for y=7 to letztezeile
    if s1.cells(y,x).interior.color=gruen then
        if s1.cells(y,x)="F" then zf1=zf1+1
        if s1.cells(y,x)="S" then zs1=zs1+1
        if s1.cells(y,x)="N" then zn1=zn1+1
    endif
    if s1.cells(y,x+1).interior.color=gruen then
        if s1.cells(y,x+1)="F" then zf2=zf2+1
        if s1.cells(y,x+1)="S" then zs2=zs2+1
        if s1.cells(y,x+1)="N" then zn2=zn2+1
    endif
next y

Trockenschwimmen, aber versuch mal so in der Art irgendwie...

Gruß, Michael

 

Hallo Michael,

Erst mal danke für deine Hilfe.

Wie du schon richtig erkannt hast, weiß ich in welcher Spalte ich suchen muss. Auch hab ich die erste und die letzte Zeile (bleibt immer die gleiche Anzahl) an Zellen, die ich in der Spalte abfrage.
Jedoch hab ich mich bei der Bedingung zum Zählen wohl nicht korrekt ausgedrückt.
In den Zellen frage ich 3 Bedingungen ab:
- Hat die Zelle den Text "F" UND Hintergrundfarbe Grün
- Hat die Zelle den Text "S" UND Hintergrundfarbe Hellblau
- Hat die Zelle den Text "N" UND Hintergdunfarbe Dunkelblau

Aber mit deinem Denkanstoß komme ich auf jedenfall weiter. Vielen Dank dafür. Wenn der Quellcode fertig und funktionstüchtig ist, werde ich ihn hier rein stellen.

regards
Klaus