PDA

Vollständige Version anzeigen : Formel Übersetzen, Anzahl unterschiedlicher Zelleinträge ermitteln


Piet.Peter
22.09.2016, 11:11
Hallo Leute,

ich brauche (mal wieder) eure Hilfe. Ich bin gerade etwas ratlos bei der Übersetzung einer Excel-Matrix-Formell in VBA. Mit dem Makrorecorder habe ich es versucht, leider sind die Formeln so nicht anwendbar. Scheinbar funktioniert es bei Matrixformeln nicht so wie bei normalen oder ich mache was falsch.

Die Formel sucht unterschiedliche Zelleinträge in einer Spalte unter mehreren Bedingungen.

Die Excel-Formel: (zur Übersichtlichkeit habe ich nur eine Bedingung angegeben)

{=SUMME(N(WENN((Ergebnis_1!A1:A1300="x")*1;
VERGLEICH(WENN((Ergebnis_1!A1:A1300="x")*1;
Ergebnis_1!B1:B1300);
WENN((Ergebnis_1!A1:A1300="x")*1;
Ergebnis_1!B1:B1300); )=ZEILE(1:1300))))}


Orange: Bedingungen
Grün: Bereich in dem Anzahl ermittelt werden soll
Blau: Zellbereich

Zur Vollständigkeit: Das Ganze soll in folgender Zelle ausgeben werden:

With Worksheet("Übersicht")

.Range("C14")= ...

End with

Ich möchte hier wirklich niemanden beleidigen, weil es zu banal ist. Ich kenne mich einfach nur nicht so gut mit den englischen Versionen der Formeln und den Formatierungen in VBA aus.

ich hoffe ihr könnt mir helfen! :)

Beste Grüße
Piet

EarlFred
22.09.2016, 11:57
Hallo Piet,

zur Übersichtlichkeit habe ich nur eine Bedingung angegeben
heißt: in Wahrheit ist die Formel länger? Dann bekommst Du vermutlich ein Problem mit der zulässigen Länge von Matrixformeln, wenn Du sie per VBA einträgst: Bei 255 Zeichen ist Schluss.

Kürze die Formel ein, am einfachsten, indem Du benannte Bereiche verwendest.

Ich habe mal 2 Namen definiert:
"A":=Ergebnis_1!$A$1:$A$1300
"B":=Ergebnis_1!$B$1:$B$1300

Dann sieht die Formel so aus (mit ein paar weiteren Änderungen über 100 Zeichen kürzer als Deine):
=SUMME(N(WENN(A="x";VERGLEICH(WENN(A="x";B);WENN(A="x";B);0)=ZEILE($1:$1300))))
VBA:
Range("K10").FormulaArray = "=SUM(N(IF(A=""x"",MATCH(IF(A=""x"",B),IF(A=""x"",B),0)=ROW($1:$1300))))"

Du kannst auch Formelteile in Namen auslagern. Musst mal schauen, welcher Aufwand hier nötig ist.

Grüße
EarlFred

Piet.Peter
22.09.2016, 12:16
Vielen Dank schonmal Earlfred!

Leider gibt er mir bei ".FormulaArray" die Fehlermeldung "Unzulässiger oder nicht ausreichend Definierter Verweis".

Liegt es vielleicht an meinen Definitionen?

Hier der Code:
Sub Übersicht_2()

Dim A As Range
Dim B As Range

Set A = Worksheets("Ergebnis_1").Range("A1: A1300")
Set B = Worksheets("Ergebnis_1").Range("B1: B1300")



Worksheet("Übersicht").Range("K14") = .FormulaArray = "=SUM(N(IF(A=""HH"",MATCH(IF(A=""HH"",B),IF(A=""HH"",B),0)=ROW($1:$1300))))"


End Sub

EarlFred
22.09.2016, 12:22
Hallo Piet,

Range("K10").FormulaArray = "=SUM(N(IF(A=""x"",MATCH(IF(A=""x"",B),IF(A=""x"",B),0)=ROW($1:$1300))))"

Grüße
EarlFred

Piet.Peter
22.09.2016, 12:24
kleiner Zusatz:

Ich habe gerade in einem anderem Post gelesen, dass man die Bereiche in R1C1-Form angeben muss. Ist das richtig und wenn ja, wie würden die Bereichsdefinitionen dann konkret aussehen?

EarlFred
22.09.2016, 12:25
Ich habe gerade in einem anderem Post gelesen
ach - nein, ich kommentier das jetzt lieber nicht.

Piet.Peter
22.09.2016, 12:30
:D :D

Ich bekomme leider immernoch eine Fehlermeldung:

Bei Worksheet("Übersicht").Range("M14")... wird "Sub oder Function nicht definiert" ausgegeben.

Weißt du woran das liegen könnte?

Und tut mir leid, dass ich kein Blatt hochladen kann...

EarlFred
22.09.2016, 12:31
Edit sagt: Fehler selbst behoben

Piet.Peter
22.09.2016, 12:37
wooow...mir ist es auch gerade aufgefallen und ich hab gehofft, dass ich mich korrigieren kann, bevor du antwortest. Aber jetzt ist die Schmach perfekt..

noch eine Frage zum Definieren der Variablen: Vermutlich muss ich die in Excel selbst definieren und nicht in VBA oder?