Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 18:49:56
merge & select
24.04.2024 17:19:09
Formel mit Index-Funktion
24.04.2024 16:19:42
SummeWenn / SummeWenns mit Bedingung wenn kleiner als dann
24.04.2024 14:48:50
"Eingabefeld" zwischen zwei Zeilen geht nicht weg.
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
700to704
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
700to704
700to704
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Problem bei Arrayformel über VBA

Problem bei Arrayformel über VBA
25.11.2005 15:45:25
Marco
Hallo Freags
Ich habe ein Problem mit einer Array Formel über VBA.
Meine Formel ist eine Teilergebniss wobei die 0 ignoriert wird.
Das Problem tritt nur auf wenn : in den abfragen ein wert mit = Bug/Heck oder Mobile Dächer
also mit / oder leerzeichen steht.
Der Code läuft bis Zeile 1003 Perfect ab der zeile1004 tritt das Phänomen auf?
Dann erscheint Laufzeitfehler 1004 Die FormulaArray-Eigenschaft des Rangeobjektes kann nicht Festgelegt werden.
Alle anderen Werte Crash oder Querschnitt haben kein Problem.
Wenn ich die Formel über Excel direkt eingebe, gibt es kein Problem.
wenn mann nochmal 100 zeilen dazu kopiert kann man den fehler sehen.
Die Abfrage wird über ein Userform gestartet. mit dem Comandbutton möglich.
die Array formel liegt in ein Modull
Seltsames Phänomen oder?
wer kann helfen
https://www.herber.de/bbs/user/28720.xls
gruß Marco

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem bei Arrayformel über VBA
27.11.2005 15:47:07
Coach
Hallo Marco,
das liegt nicht direkt an den Bezeichnungen,sondern an der maximal zulässigen Länge der Arrayformel, die wiederum nicht absolut ist, sondern von der Menge+Art der Definition der Bezüge etc. abhängt, wie folgender Test zeigt:

Sub CheckFormelLaenge()
Dim i As Long, Formel$
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """)*(D5:D9999=""E1"")*(I5:I9999=""9911"")*(G5:G9999 >=AB2)*(G5:G9999<=AM2)*(L5:L9999>0),L5:L9999))")
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """)*(D5:D9999=""E1"")*(I5:I9999=""9911"")*(G5:G9999 >=AB2)*(G5:G9999<=AM2),L5:L9999))")
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """)*(D5:D9999=""E1"")*(I5:I9999=""9911"")*(G5:G9999 >=AB2),L5:L9999))")
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """)*(D5:D9999=""E1"")*(I5:I9999=""9911""),L5:L9999))")
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """)*(D5:D9999=""E1""),L5:L9999))")
Call MaxLaenge("=AVERAGE(IF((B5:B9999 = """, """),L5:L9999))")
End Sub



Sub MaxLaenge(Formel1$, Formel2)
Dim i As Long, Formel$
On Error GoTo Fehler
i = 1
Do
Formel = Formel1 & Replace(Space(i), " ", "A") & Formel2
Range("AN4").FormulaArray = Formel
i = i + 1
Loop Until False
Exit Sub
Fehler:
Debug.Print i, Len(Formel)
End Sub

Ergibt:
8 126
37 142
76 165
117 189
151 205
183 221
Maximal kann eine ArrayFormel m.W. bei VBA-Zuweisung 256 Zeichen lang sein.
Also entweder in den Restriktionen von Excel bleiben oder die Auswertung redesignen.
Da bietet sich zunächst vor allem eine Pivot an. Solltest Du die nicht wollen, wären Arrayformeln direkt in der Tabelle unter Verwendung der Bereich.Verschieben-Formel wegen der variablen Listenlänge sinnvoll.
Viele Grüße
Coach
Anzeige
AW: Problem bei Arrayformel über VBA
28.11.2005 09:09:23
Marco
Hallo Coach
Danke für die ausführliche Antwort und Entschuldigung für die spähte Rückmeldung, ich hatte über da Wochennde nicht die Gelegendheit mich damit zu beschäftigen.
Soweit habe ich es Verstanden. Mit Pivot arbeite ich nicht. In der Arrayformeln direkt in der Tabelle unter Verwendung der Bereich.Verschieben-Formel wegen der variablen muß ich mal versuchen.
Danke nochmals für die Ausführliche Antwort
Gruß Marco
AW: Problem bei Arrayformel über VBA
28.11.2005 11:07:28
Marco
Hallo Coach
Ich habe die Formel direkt in der Tabelle unter Verwendung der Bereich fest eingefügt, und übernehme Daten von der Userform in Cellen AB1, AC1, AD1 und verweise auf 65536 zeilen.
Soweit Funktioniert dei Formel, braucht nur 2 Sekunden länger.
rngAB1.Value = Cmb6umbauart.Value
rngAD1.Value = Cmb7baureihe.Value
rngAC1.Value = Cmb8kostenstelle.Value
=MITTELWERT(WENN((B5:B65536=AB1)*(D5:D65536=AD1)*(I5:I65536=AC1)*(G5:G65536 >=AB2)*(G5:G655360);L5:L65536))
gruß
Marco
Anzeige
AW: Problem bei Arrayformel über VBA
28.11.2005 12:42:35
Coach
Hallo Marco,
prinzipiell funktioniert das mit bereich.verschieben (am Beispiel von (B5:B65536=AB1) ) so:
(BEREICH.VERSCHIEBEN($B$4;1;;ANZAHL2($B:$B)-1;)=AB1)
Durch die Begrenzung der Zeilen funktioniert die Rechnung i.d.R. deutlich schneller.
Du solltest immer nur in einer Spalte zählen (die in allen Zeilen gefüllt ist) oder besser noch die Zeilenzahl in einer Zelle berechnen und darauf verweisen. Der Offset um 1 Zeile 1 empfiehlt sich, falls Du mal die 1. Zeile löschst etc.
Viele Grüße
Coach
AW: Problem bei Arrayformel über VBA
28.11.2005 09:09:39
Marco
Hallo Coach
Danke für die ausführliche Antwort und Entschuldigung für die spähte Rückmeldung, ich hatte über da Wochennde nicht die Gelegendheit mich damit zu beschäftigen.
Soweit habe ich es Verstanden. Mit Pivot arbeite ich nicht. In der Arrayformeln direkt in der Tabelle unter Verwendung der Bereich.Verschieben-Formel wegen der variablen muß ich mal versuchen.
Danke nochmals für die Ausführliche Antwort
Gruß Marco
Anzeige

124 Forumthreads zu ähnlichen Themen

vba-Datenexport nach Excel2007 -Array mit 70000 DS
Tim

Hallo,
ich habe einen Datenexport aus einem Programm nach Excel in vba. Das ganze funktioniert soweit ganz gut.
Nun möchte ich jedoch nicht, das sjede Zeile aus meinem Programm exportiert wird, daher gibt es ein Array mit entsprechenden Datensätzen: Entweder eine 1 (steht für Export der...
20.08.2008 08:57:00
Thread-ID: 1003084
2 Beiträge
Anzeige
VBA FormulaArray nur 256 Zeichen
Werner

Hallo Zusammen,
ich möchte Formeln einer ziemlich umfangreichen Jahresarbeitsplanung in Excel, per VBA-Code korrigieren, da diese Datei(en) x-mal vervielfältigt und "beplant" sind. (über 100 Dateien mit mittlerweile über 28.000 Formeln je Datei)
Bei einer bestimmten Matrixformelgröße, ü...
25.10.2008 21:25:22
Thread-ID: 1019276
9 Beiträge
Probleme bei Sortierung von 2-dimensionalem Array
2-dimensionalem

Hallo,
ich habe leider ein Problem bei der Sortierung eines 2-dimensionalen Arrays. Die Daten an das Array werden von einer Listbox übergeben. Ich erhalte immer die Fehlermeldung "Fehler beim Kompilieren: Unverträglicher Typ: Datenfeld oder benutzerdefinierter Typ erwartet". Der Text "Arr"...
23.07.2009 14:43:19
Thread-ID: 1090821
2 Beiträge
Anzeige
Array Redim Preserve in 1. Dimension?
Andreas

Guten Abend Excel Freunde,
ich habe beim Stöbern im Forum einen VBA Code entdeckt, der mein Interesse geweckt hat. Die Datei wurde von Tino erstellt. Die Meriten gehen eindeutig an ihn. Der in dieser Datei enthaltene Code füllt eine ListBox u.a. unter Verwendung von Arrays. Ein zweidimensio...
27.04.2010 20:49:14
Thread-ID: 1154168
10 Beiträge
2Dimensionales Array sortieren
jowe

Hallo,
habe gerade viele Beiträge zum Thema gelesen - leider habe ich bislang für meine Frage keine Lösung gefunden oder die gefundenen Lösungen nicht für mich umsetzen können oder vielleicht auch nicht verstanden. Daher brauch ich jetzt mal Eure Hilfe: Meine Tabelle enthält in einer belieb...
18.07.2010 19:47:48
Thread-ID: 1167644
9 Beiträge
VBA - CopyFromRecordset für Array
Jan Kittler

Hallo, mit der CopyFromRecordset-Methode kann man sehr effizient große Datenmengen in ein Tabellenblatt "schaufeln". Gibt es eine vergleichbare Methode auch zum Füllen eines Arrays?Grüße aus Dresden
Jan
07.05.2002 09:32:19
Thread-ID: 119065
4 Beiträge
2-dimensionales Array: Doppelte "Zeilen" löschen
Martin

Hallo,
ich lese aus dem Internet etwa 7650 Zeilen mit 13 Spalten in ein Array in Excel ein, was auch sehr gut klappt. Leider kommen einige Zeilen doppelt vor, die ich daher gerne aus dem Array löschen würde. Kennt von Euch jemand eine Function, die alle doppelten Zeilen eines 2-dimensionale...
20.11.2010 15:23:58
Thread-ID: 1187936
10 Beiträge
2-dimensionales Array Sortieralgorithmus
Andi

Hallo,
zuvor habe ich Daten auf einem Excelsheet sortiert.
Jetzt soll aber das ganze auf ein Formular / Tabelle. Auf dem Formular habe ich das Steuerelement "Spreadsheet" von Microsoft eingefügt.
Wie bekomme ich meine Daten sortiert? Muß mehrere Felder sortieren, Nummer aufsteigen...
02.11.2011 17:09:28
Thread-ID: 1236340
2 Beiträge
Anzeige

Links zu Excel-Dialogen

UserForm

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
1
5
8
1
4
9
8
3
9
1
4
5
3
8
13
19
3
5
2
13
6
4
3
9
2