Probleme mit .formulaarray

Hallo Forumgemeinde,
erstmal vielen Dank für die vielen Tipps und Lösungen die ich bisher hier im Forum gefunden habe! Nun möchte ich selbst eine Frage stellen, da ich auch nach langer Internetrecherche und Ausprobieren nicht auf meinen Fehler komme.
Es geht um diese Formel in meinem Code


With wsData.Range(ColumnToLetter(ColEndeVorg) & FirstRow & ":" & ColumnToLetter(ColEndeVorg) &   _
_
_
x)
.FormulaArray = "=if(rc" & ColOrt & """in Zukunft"",MAX(IF((R" & FirstRow & "C" & ColFA & ":R[ _
_
_
-1]C" & ColFA & "=RC" & ColFA & "),R" & FirstRow & "C" & ColEnde & ":R[-1]C" & ColEnde & ",RC" & _
_
ColEnde & ")),"""")"
.Value = .Value
.NumberFormat = "dd.mm.yyyy"
End With

ColEndeVorg, ColFA, ColEnde sind dabei festgelegte Spaltenindizes (as integer), FirstRow die erste Datenzeile (Zeile 6) und x die letzte beschriebene Zeile (wird zuvor ermittelt. Die Formel funktioniert leider nicht...
Das "Problem" steckt bereits in folgendem Teil der aus übersichtlicheren und vereinfachten _
Formel, der auch zur Arrayrechenweise zwingt:


With wsData.Range("w6:w" & x)
.FormulaArray = "=MAX(IF((R6C2:R[-1]C2=RC2),R6C16:R[-1]C16,RC16))"
.Value = .Value
.NumberFormat = "dd.mm.yyyy"
End With

Ich dachte bisher, dass man .FormulaArray genau wie .FormulaR1C1 direkt in eine gesamte Spalte/Spaltenbereich schreiben kann. Wenn ich dass mache, steht aber in jeder Zeile der Bezug zur ERSTEN Zeile (also hier Zeile 6). Bspw. RC2, was normalerweise in Zeile 7 dann R7C2 ergibt etc. scheint nicht zu funktionieren. Auch R[]C2 oder R[0]C2 löst bei mir das Problem nicht.
Nun habe ich gefunden, bereits zwei Lösungsvarianten im Netz gefunden, die bei mir aber beide auch nicht einwandfrei funktionieren und mir auch mehr als Umgehung des Problems anstatt Lösung erscheinen.
1.
Die Formel als .FromulaR1C1 eingeben und anschließend


.FormulaArray=.FormulaR1C1

was getestet mit einigen Zeilen auch funktioniert, aber bei meiner vollständigen Liste ( _ aufgrund der Input-Datenquelle über 67 000 Zeilen, die ich anschließend über Pivot erfolgreich aussortiere/auswerte) einen Laufzeitfehler 13: Typen unverträglich liefert. Ich habe schon viele Varianten ausprobiert und festgestellt, dass der Code bis x= 65 541 funktioniert und bei 64 542 abbricht - also könnte ich den Bereich splitten und auf 2 Mal die Formel eintragen. Allerdings bricht der Teil


.value =. value

ab mit der Fehlermeldung Überlauf. Dies lässt sich wiederum umgehen, wenn man die gesamte Spalte kopiert über .copy und dann über pastespecial xlpastevalues nur die Werte einfügt - aber wieder nur ein Umweg...
2.
Die Array-Formel nicht in den gesamten Bereich, sondern nur in die erste Zeile einfügen und dann kopieren.

With wsData.Range("w6")
.FormulaArray = "=MAX(IF((R6C2:R[-1]C2=RC2),R6C16:R[-1]C16,RC16))"
.copy Destination:= wsData.Range("w7:w" & x)
.Value = .Value
.NumberFormat = "dd.mm.yyyy"
End With

Gleiches Problem bei .value =.value ...
Weis jemand wie ich das Problem richtig löse und am Besten in den Bereich wsData.Range("w6:w" & x) direkt die Arrayformel eintragen kann? Was mache ich falsch, dass bei mir der Bezug immer auf die erste Zeile ist?
Irgendwie ist für mich .FormulaArray wie ein Buch mit Sieben Siegeln - viel gelesen, aber immer noch nicht komplett verstanden...
Irgendwelche andere Vorschläge?
Sorry, dass es so lange geworden ist, aber jetzt wisst ihr, was ich schon alles ausprobiert habe, und dass es immer noch nicht funktioniert :)
Vielen Dank für jeden Tipp!
Gruß
Maria

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub testFmlBer() Const adQBer$ = "A1:A3", adZBer$ = "B1:B3", _ txQWt$ = "{1;2;3}", txZWt$ = "{""=A1"";""=A2"";""=A3""}" Dim QWt, ZWt QWt = Evaluate(txQWt): ZWt = Evaluate(txZWt) Range(adQBer) = QWt: Range(adZBer).Formula = ZWt End Sub

Sub testMaria() Const naWsh$ = "Tabelle1" Dim x As Long, wsData As Worksheet Set wsData = ThisWorkbook.Sheets(naWsh) For x = 6 To 10 With wsData.Cells(x, 23) .FormulaArray = "=MAX(IF(R6C2:R[-1]C2=RC2,R6C16:R[-1]C16,RC16))" ' .Value = .Value .NumberFormat = "dd.mm.yyyy" End With Next x End Sub

Sub testMaria1() 'Anm: Nur f.1zellige MxFmln! Const anfZl As Long = 6, endZl As Long = 10, relSp As Long = 23, naWsh$ = "Tabelle1", _ txMxFml$ = "=MAX(IF(R6C2:R[-1]C2=RC2,R6C16:R[-1]C16,RC16))" Dim x As Long, wsData As Worksheet Set wsData = ThisWorkbook.Sheets(naWsh) For x = anfZl To endZl With wsData.Cells(x, relSp) .FormulaArray = txMxFml: .NumberFormat = "dd.mm.yyyy": .Value = .Value End With Next x Set wsData = Nothing End Sub Sub testMaria2() 'Anm: Nur f.1zellige MxFmln! Const anfZl As Long = 6, endZl As Long = 10, relSp As Long = 23, naWsh$ = "Tabelle1", _ txMxFml$ = "=MAX(IF(R6C2:R[-1]C2=RC2,R6C16:R[-1]C16,RC16))" Dim fmAnz As Long, relBer As Range, wsData As Worksheet Set wsData = ThisWorkbook.Sheets(naWsh): fmAnz = endZl - anfZl + 1 With wsData Set relBer = .Range(.Cells(anfZl, relSp), .Cells(endZl, relSp)) End With With WorksheetFunction ' relBer.FormulaArray = txMxFml 'Anm: Nur f.m-zellige MxFmln! relBer.FormulaArray = .Transpose(Split(LTrim(.Rept(" " & txMxFml, fmAnz)))) End With With relBer .NumberFormat = "dd.mm.yyyy": .Value = .Value End With Set relBer = Nothing: Set wsData = Nothing End Sub

Hallo Luc,
Vielen, vielen herzlichen Dank!!! Ich habe deine letzte Variante genommen und angepasst. Super, schnell und elegant! Da wäre ich NIE von selbst drauf gekommen!
Ich habe auch zugegebener Maßen etwas gebraucht um alle Codebestandteile zu verstehen und antworte deshalb auch erst zwei Tage später :)
Auch mit deiner Anmerkung, dass ich es mir schwerer mache als nötig ist, hast du sicherlich Recht.
Für mich war schwer einzuschätzen, welche Informationen ihr benötigt, oder was nur zusätzlich verwirrt (Und meine Frage ist ja schon eine Seite voll geworden ;))
Insgesamt ist mein Ziel eine Datei zu erstellen, bei dem man auf dem ersten Tabellenblatt bei mehren DropDown Felder seine Auswahl treffen kann und andere DropDownfelder dann teilweise die Auswahlmöglichkeit einschränken. Bei jedem Klick/Auswahl wird über den Code ein Diagramm angepasst und dann eben nur Daten entsprechend der aktuellen Auswahl dargestellt. Der Filter läuft über eine Pivottabelle.
Hierfür habe ich schon einige Module geschrieben und soweit funktioniert auch alles reibungslos.
Jetzt zu dem Problemfall im Speziellen. Bei meiner Datenquelle, die ich auch über VBA importiere stehen Aufträge mit allen nötigen Arbeitsfolgen und falls erledigt einem gemeldeten Fertig-Termin.
Starttermine für die Folgen gibt es keine, benötige ich aber für eine Darstellung auf dem Zeitstrahl (wie viele Aufträge hat/hatte wer wann in Bearbeitung? Wieviel kann er abarbeiten? Ist er überlastet etc.)
Die Logik ist nun, dass ich für den Starttermin der Arbeitsfolge den Endtermin des Vorgängers nehme (alle Arbeitsfolgen sind sequenziell). Allerdings geht es nicht, dass ich immer nur die Zeile oberhalb nehme, da es "richtige" Arbeitfolgen und Arbeitsfolgen "nur zur Info" (diese sind ohne Endtermin und sind erkennbar über ein besonderes Merkmal in einer anderen Spalte) gibt.
Also ist mein Ziel meiner Formel, das "letzte Datum" / "höchste Datum" / max zu nehmen, mit der Bedingung, dass es immer noch die gleiche Auftragsnummer ist und nur Vorgänger und nicht die Arbeitsfolge selbst oder irgendwelche Nachfolger berücksichtigt werden...
Aber dies alles nur zur Verdeutlichung...
Nochmal vielen herzlichen Dank. Es funktioniert mit einer vertretbaren Laufzeit und ich bin happy! Und außerdem habe ich noch viele andere Sachen gelernt wie das mit dem $ für String und dass ich durch einen Doppelpunkt nicht immer eine neue Zeile benötige und und und...
Schöne Grüße
Maria

Beliebteste Forumthreads (12 Monate)

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread