Office: ZÄHLENWENN als VBA Code

Hallo,

ich bin neu in diesem Forum und auch in VBA ein Anfänger. Ich habe folgendes Problem:

Ich habe ein kleines Makro geschrieben und jetzt das Problem, daß dieses Makro noch einen kleinen Kaken hat.

Das Makro:

Sub TabellenfunktionErfassen()
Dim i As Long

Sheets("Anwesenheit2").Activate
Range("E1").Select

For i = 1 To ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaLocal = "=ZÄHLENWENN(Anwesenheit!E2:E56000;C1)"
ActiveCell.Offset(1, 0).Select
Next i
End Sub


Wie gesagt, im Großen und Ganzen läuft es - aber!!

Es holt mir aus der Tabelle "Anwesenheit" aus der Spalte "E" die Häufigkeit des Auftretens einer Kd.-Nr. und schreibt mir diese Häufigkeit auch in die Tabelle "Anwesenheit2" in die erste Zeile in Spalte "E", das heiß genau in die Zeile in der auch in Spalte C die einzelne passendvorkommende Kd.-Nr. aufgelistet ist. Dann jedoch schreibt es auch in alle weiteren Zeilen in Spalte "E" der Tabelle "Anwesenheit2" die gleiche Häufigkeit zu den weiteren gelisteten Kd.-Nr. und zwar aus dem Grund, weil ich in der Formel bei jedem Durchlauf -C1- nicht schrittweise erhöhen kann. Wie muß ich da verfahren, um auch C1 in der Formel bei jedem Durchlauf hochzuzählen (C1, C2, C3....).
Gibt es da vielleicht eine reine Lösung in VBA (ich habe mir schon "fast" den Kopf zerbrochen und auch - das gebe ich zu - in einem andern Forum gefragt, aber keine adäquate Lösung bekommen.

Ich bin für jeden Ansatz dankbar

Hier ansatzweise die Tabellen:

(Tabelle - Anwesenheit2 - Zieltabelle)

[EDIT]: Daten auf Wunsch des Themenerstellers gelöscht!


Ausgangtabelle "Anwesenheit"

[EDIT]: Daten auf Wunsch des Themenerstellers gelöscht!


Bis dahin

Andreas

:)

 

Hallo Andreas,

also, wenn es nur darum geht, dass er das C1 hochzählt, würde ich die 1 einfach auf den zähler (i) beziehen. Aussehen würde das dann so (nur die Zeile):
Code:

Hoffe es hilft.

 

Hallo Andreas,

schreibe die Funktion direkt ohne Schleife in die Zellen :

VBA-Code:

Sub TabellenfunktionErfassen2()     With Sheets("Anwesenheit2")         .[E1].Resize(.UsedRange.Rows.Count).Formula = "=Countif(Anwesenheit!E:E000,C1)"     End With End Sub

Code eingefügt mit Syntaxhighlighter 4.15

 

Hallo NoNet,

interessante Vorgehensweise mit der Verwendung von "Resize".
Hast du ggf. noch weitere Muster für andere Anwendungen?
Versuche mir das gerade auszumalen.

 

Hallo Woody,

ich verwende keine Muster oder Vorlagen, sondern erstelle die Lösungen jeweils frei nach Bedarf (gewisse Routine-Formulierungen im Code wie z.B. Ermittlung der letzten Zeile etc. habe ich natürlich wie die meisten VBA-Fans jederzeit abrufbar im Kopf - also reine stumpfsinnige reproduktive "Arbeit" ;-).

Falls Du jedoch eine andere Verwendung der .Resize()-Methode meinst :
Ich verwende das sehr häufig im Zusammenhang mit ARRAY-Variablen, die erst zur Laufzeit des Codes mit Inhalten gefüllt werden und deren Ausprägung (also die Anzahl der Elemente im ARRAY) zum Zeitpunkt der Codeerstellung noch nicht bekannt sind. Hier ein Beispiel (mit vorgegebenem ARRAY- das einfach erweitert und reduziert werden kann - ohne Beinträchtigung der Funktionalität des Codes) :

VBA-Code:

Sub Woody_Pattern()     'Woody-Patten = hölzernes Muster ;-)     Dim arZellen     arZellen = Array("Woody", "Muster", "NoNet", #2/6/2009#, "Test", 12345, "Ende")     [A1].Resize(, UBound(arZellen) + 1) = arZellen End Sub

Code eingefügt mit Syntaxhighlighter 4.15

 

Merci,

wollte es mal in Zeilen untereinander versuchen.

[A4].Resize(UBound(arZellen) + 1,) = arZellen

, ging aber nicht

 

Hallo Woody,

doch, das funktioniert schon - Du musst nur das Komma weglassen und die Matrix (also das ARRAY) transponiert übergeben :

[A1].Resize(UBound(arZellen) + 1) = Application.Transpose(arZellen)

Das erste Argument der Resize()-Methode gibt die Anzahl ZEILEN an und muss immer angegeben werden - auch wenn es leer ist. Das zweite Argument hingegen spezifiziert die Spaltenanzahl und kann auch weggelassen werden - daher ohne Komma (=> nur EIN Argument) - wenn alle Daten in die gleiche Spalte übertragen werden sollen !

VBA-Code:

Sub Woody_Pattern()     'Woody-Patten = hölzernes Muster ;-)     Dim arZellen     arZellen = Array("Woody", "Muster", "NoNet", #2/6/2009#, "Test", 12345, "Ende")     [A1].Resize(, UBound(arZellen) + 1) = arZellen 'Matrix in EINE Zeile, MEHRERE Spalten     [A2].Resize(UBound(arZellen) + 1) = Application.Transpose(arZellen) 'Matrix in MEHRERE Zeilen, EINE Spalte End Sub

Code eingefügt mit Syntaxhighlighter 4.15

 

Da wäre ich nicht drauf gekommen, danke für das Muster.

 

Hallo,

danke erst einmal an alle, die mir Hilfe angeboten haben. Ich bin leider neu in idesem Forum und mußtemich erst einmal technisch damit vertraut machen.
Besondern Dank an Flämmchen - Dein Vorschlag funktioniert wunderbar - warum in die Ferne schweifen, wenn das Glück so nahe liegt!!

Jetzt werde ich erst mal das ganze "Neue" in mein kleines Programm einbinden und dann komme ich bestimmt mit der näüchsten Frage!!!

Also nochmal - vielen Dank an alle.

Bis dahin

Andreas

 

Hallo zusammen,

jetzt mein nächstes Problem:

ich habe ja nun Dank "Flämmchen" genau die Lösung gefunden, die ich gesucht habe und diese auch gleich ein wenig für ein anderes Problem umformuliert und siehe da alles klappt. Aber nun stehe ich wieder vor der ersten Lösung und muß da noch einen draufsetzen.

Ich habe ja nun aus Tabelle "Anwesenheit" - Spalte "E" die vorhandenen Kd-Nr. rausgezählt un die erhaltene Anzahl dann in der Tabelle Anwesenheit2 den entspr. Kd-Nr. zugeordnet.
Jetzt habe ich in Tabelle "Anwesenheit" ein weiteres Kriterium nämlich "K" für Krank in der Spalte "M", das ich unter Berücksichtigung der Eingangs auch in dieser Tabelle gezählten Kd-Nr's zählen will und in die Tabelle "Anwesenheit2" in die Spalte "G" schreiben will und zwar in die Zeile mit der passenden Kd.-Nr. in Spalte "C".


Also habe ich hier zwei mal das Kriterium "Zählen" - wenn in Tabelle Anwesenheit in Spalte "E" die Kd.-Nr. "XY" auftaucht und in Spalte "M" der gleichen Tabelle der Buchstabe "K", dann zähle die Häufigkeit von "K" und schreibe diese in Tabelle "Anwesenheit2" in die Spalte "G" in die richtige Zeile mit der passenden Kd.-Nr. die aus Spalte "C" dieser Tabelle ausgelesen werden kann.

Ich hoffe, mich verständlich ausgedrückt zu haben


Bis dahin

Andreas

Nachgereicht:

Sub KrankErfassen()
Dim i As Long

Sheets("Anwesenheit2").Activate
Range("G1").Select

For i = 1 To ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaLocal = "=SUMME(WENN((Anwesenheit!E2:E56000;C" & i & ") _ *(Anwesenheit!M2:M56000;""K""))"
ActiveCell.Offset(1, 0).Select
Next i
End Sub


Hier mein Versuch, das Problem zu lösen - aber bis dato ohne Erfolg (1004, Anwendungs- Objektdefinierter Fehler). Irgendwo ist der Wurm drin

 

Hallo nochmal alle "Helfer"

ich habe mir überlegt, die ganze Sache in zwei Schritten zu erledigen.

1) Ich habe in der Spalte "K" zwei Variablen stehen. Einmal ein VZ für Vollzeit und ein TZ für Teilzeit. Jetzt möchte ich dem VZ den Wert 8 zuordnen und dem TZ den Wert 4,
Dann habe ich in der Spalte "M" die Variablen A, K, E, U, S, TZA stehen, denen ich folgende Werte zuordnen will. Dem A, U, TZA, S jeweils eine 1 und den anderen (K, E) eine 0.
Nun möchte ich die jeweiligen Spaltenprodukte bilden (K * M) und damit erhalte ich als Ergebnis entweder eine 8 eine 4 oder eine 0 in der dafür vorgesehenen Ergebnisspalte "P"

Nun brauche ich nur noch die Spaltensumme der Ergebnisspalte für ein bestimmtes Kriterium bilden und in die dafür vorgesehene Spalte der Ergebnistabelle schreiben.

Die Frage: Wie kann ich die o.g. Zuordnung der Variablen zu den Werten bewerkstelligen und dann als Produkt in eine neue Spalte übertragen.
Ich suche eine Lösung in VBA. Hauptsächlich geht es mir hier um die Zuweisung der Variablen zu Zahlen und die Umsetzung der Produktbildung in einer Schleife.

Danke

Andreas

 

Hallo,

habe meine zuvor beschriebenen Probleme gelöst und nun nur noch ei kleines zu bewältigen.


Mein Problem:

Ich habe in der Spalte "D" meiner Tabelle in den einzelnen Zellen immer ein VZ oder TZ für Vollzeit oder Teilzeit stehen je nach angesprochenem Teilnehmer. Nun will ich diesen in der Spalte "P" für VZ eine 8 und TZ eine 4 zuordnen. Wie kann ich diesen Übergang hinkriegen.

Vielen Dank

Bis dahin

Andreas

 

Hallo alle zusammen,

habe meine kleinen problemchen mittlerweile gelöst und will mich an dieser Stelle nur noch mal bedanken!

Bis zum nächsten Problem

Andreas