Gruppen
- Top Gruppen
- Betriebsysteme
- Windows classik
- Linux
- BS-Sonstige
- Software
- Datenbanken
- Bildbearbeitung
- Audio/mp3/Video
- Security/Viren
- SW-Sonstige
- Hardware
- Mainboard/CPU
- Grafikkarten
- Peripherie
- Laptop/Notebook
- HW-Sonstiges
- Netzwerk
- Telefon,ISDN,Modem
- W-Lan
- NW-Sonstiges
- Programmierung
- PHP,ASP,Perl...
- Java,C++...
- Webseiten/HTML
- Sonstiges
- PC-Sonstiges
- Plauderecke
- SN Intern
- Test
/ Forum / Tabellenkalkulation
Tabellenkalkulation
Fragevon Nicole2 vom 26.11.2020, 15:33 Options
Makro mit formeln überlastet?
Hallo zusammen,
ich habe ein Makro, das mir Formeln zur Aktualisierung von Daten je nach Datum in einen besimmten Datenbereich kopiert.
Bisher hat dies auch ganz gut funktioniert. Scheinbar gibt es aber ein Problem, wenn die Formeln zu aufwändig werden, beispielsweise:
SUMMENPRODUKT((VERKETTEN('[Detail Logistics Services V1_8 BSP.xls]Result'!$A$8:$A$65000;'[Detail Logistics Services V1_8 BSP.xls]Result'!$B$8:$B$65000)=VERKETTEN($H5;$I5))*('[Detail Logistics Services V1_8 BSP.xls]Result'!$L$8:$L$65000="PA2")*('[Detail Logistics Services V1_8 BSP.xls]Result'!$E$8:$E$65000))
In dem Fall bekomme ich den Laufzeitfehler 1004: anwendungs-oder objektbezogener Fehler.
Kann mir jemand sagen, was ich dagegen tun kann? Erstaunlicherweise ist es kein Problem, wenn ich die Formel manuell einkopiere und auch nicht, wenn der zu durchsuchende Datenbereich kleiner ist.
Danke, Gruß
Nicole
ich habe ein Makro, das mir Formeln zur Aktualisierung von Daten je nach Datum in einen besimmten Datenbereich kopiert.
Bisher hat dies auch ganz gut funktioniert. Scheinbar gibt es aber ein Problem, wenn die Formeln zu aufwändig werden, beispielsweise:
SUMMENPRODUKT((VERKETTEN('[Detail Logistics Services V1_8 BSP.xls]Result'!$A$8:$A$65000;'[Detail Logistics Services V1_8 BSP.xls]Result'!$B$8:$B$65000)=VERKETTEN($H5;$I5))*('[Detail Logistics Services V1_8 BSP.xls]Result'!$L$8:$L$65000="PA2")*('[Detail Logistics Services V1_8 BSP.xls]Result'!$E$8:$E$65000))
In dem Fall bekomme ich den Laufzeitfehler 1004: anwendungs-oder objektbezogener Fehler.
Kann mir jemand sagen, was ich dagegen tun kann? Erstaunlicherweise ist es kein Problem, wenn ich die Formel manuell einkopiere und auch nicht, wenn der zu durchsuchende Datenbereich kleiner ist.
Danke, Gruß
Nicole
Antwort 1 von Nicole2 vom 26.11.2020, 15:36 Options
Sorry, das Makro ist für diese Frage vielleicht auch interessant:
Sub Daten_aktualisieren()
Dim raZelleStart As Range
Dim raZelleEnde As Range
With Worksheets("Data input")
Set raZelleStart = .Columns(4).Find(.Range("E2"))
Set raZelleEnde = .Columns(4).Find(.Range("F2"))
Range(Cells(raZelleStart.Row, 10), Cells(raZelleEnde.Row, 243)).FormulaLocal = .Range(.Cells(raZelleStart.Row, 10), .Cells(raZelleEnde.Row, 243)).FormulaLocal
End With
Dim zStart As Long
Dim zEnde As Long
Dim sStart As Integer
Dim sEnde As Integer
Dim strFormula As String
zStart = raZelleStart.Row
zEnde = raZelleEnde.Row
sStart = Range("J5").Column
sEnde = Range("II5").Column
'gehe alle Zeilen durch
For i = zStart To zEnde
'gehe alle Spalten in aktueller Zeile durch
For j = sStart To sEnde
'lese Formel aus
strFormula = ActiveSheet.Cells(5, j).Formula
'tausche "5" gegen aktuelle zeile-nr
strFormula = Replace(strFormula, "5", CStr(i))
'schreibe die Formel in die Zelle rein.
ActiveSheet.Cells(i, j).Formula = strFormula
Next
Next
MsgBox "Fertig"
End Sub
gru´ß
nicole
Sub Daten_aktualisieren()
Dim raZelleStart As Range
Dim raZelleEnde As Range
With Worksheets("Data input")
Set raZelleStart = .Columns(4).Find(.Range("E2"))
Set raZelleEnde = .Columns(4).Find(.Range("F2"))
Range(Cells(raZelleStart.Row, 10), Cells(raZelleEnde.Row, 243)).FormulaLocal = .Range(.Cells(raZelleStart.Row, 10), .Cells(raZelleEnde.Row, 243)).FormulaLocal
End With
Dim zStart As Long
Dim zEnde As Long
Dim sStart As Integer
Dim sEnde As Integer
Dim strFormula As String
zStart = raZelleStart.Row
zEnde = raZelleEnde.Row
sStart = Range("J5").Column
sEnde = Range("II5").Column
'gehe alle Zeilen durch
For i = zStart To zEnde
'gehe alle Spalten in aktueller Zeile durch
For j = sStart To sEnde
'lese Formel aus
strFormula = ActiveSheet.Cells(5, j).Formula
'tausche "5" gegen aktuelle zeile-nr
strFormula = Replace(strFormula, "5", CStr(i))
'schreibe die Formel in die Zelle rein.
ActiveSheet.Cells(i, j).Formula = strFormula
Next
Next
MsgBox "Fertig"
End Sub
gru´ß
nicole
Antwort 2 von rainberg vom 26.11.2020, 16:03 Options
Hallo Nicole,
habe Dein Makro nicht getestet, aber wenn Du die obige Formel in viele Zellen kopierst, kann es schon zu einer Überlastung des Speichers kommen.
Als erstes würde ich prüfen, ob es wirklich erforderlich ist, die Formel auf 65000 Zeilen auszulegen und evtl. anpassen.
Eine weitere Aussage kann ich wegen Unkenntnis der Datei nicht leider treffen.
Gruß
Rainer
habe Dein Makro nicht getestet, aber wenn Du die obige Formel in viele Zellen kopierst, kann es schon zu einer Überlastung des Speichers kommen.
Als erstes würde ich prüfen, ob es wirklich erforderlich ist, die Formel auf 65000 Zeilen auszulegen und evtl. anpassen.
Eine weitere Aussage kann ich wegen Unkenntnis der Datei nicht leider treffen.
Gruß
Rainer
Antwort 3 von Nicole2 vom 26.11.2020, 16:09 Options
Hallo Rainer,
leider brauche ich die 65000 Zeilen tatsächlich.
Warum kommst es zu der Überlastung des Speichers bei der Ausführung des Makros, nicht aber, wenn ich die Formeln ganz normal einkopiere?
Danke, Gruß
Nicole
leider brauche ich die 65000 Zeilen tatsächlich.
Warum kommst es zu der Überlastung des Speichers bei der Ausführung des Makros, nicht aber, wenn ich die Formeln ganz normal einkopiere?
Danke, Gruß
Nicole
Antwort 4 von rainberg vom 26.11.2020, 16:26 Options
Hallo Nicole,
es kommt nicht zur Überlastung des Makros, aber es kann zur Überlastung des Speichers oder des Systems generell kommen.
Matrixformeln sind Recoursenfresser und jede Formel wird in dem Moment berechet, in dem sie einkopiert wird.
Deaktiviere doch mal die automatische Berechnung und führe das Makro noch mal aus, vielleicht ist dann der Fehler weg.
Gruß
Rainer
es kommt nicht zur Überlastung des Makros, aber es kann zur Überlastung des Speichers oder des Systems generell kommen.
Matrixformeln sind Recoursenfresser und jede Formel wird in dem Moment berechet, in dem sie einkopiert wird.
Deaktiviere doch mal die automatische Berechnung und führe das Makro noch mal aus, vielleicht ist dann der Fehler weg.
Gruß
Rainer
Antwort 5 von nighty vom 26.11.2020, 16:33 Options
hi all :-)
255 zeichen sind das maximum der zu darstellenden formel :-)
nutze makros die die berechnung durchfuehren ,eventuell durch ereignisroutinen ausgeloest
gruss nighty
255 zeichen sind das maximum der zu darstellenden formel :-)
nutze makros die die berechnung durchfuehren ,eventuell durch ereignisroutinen ausgeloest
gruss nighty
Antwort 6 von Nicole2 vom 26.11.2020, 16:34 Options
Hallo Rainer,
leider funktionier es auch nicht, wenn ich die automatische Berechnung ausschalte.
Hast du sonst noch eine Idee? Oder vielleicht kann mir noch jemand anders bei dem Problem helfen?
Danke, Gruß
Nicole
leider funktionier es auch nicht, wenn ich die automatische Berechnung ausschalte.
Hast du sonst noch eine Idee? Oder vielleicht kann mir noch jemand anders bei dem Problem helfen?
Danke, Gruß
Nicole
Antwort 7 von nighty vom 26.11.2020, 16:38 Options
hi all :-)
ups ,ich zaehlte 308 zeichen *grrr*
gruss nighty
ups ,ich zaehlte 308 zeichen *grrr*
gruss nighty
Antwort 8 von Nicole2 vom 26.11.2020, 16:39 Options
Hallo nighty,
die sache mit den zeichen scheint zunächst plausibel, allerdings funktiioniert die formel, wenn ich sie manuell einkopiere und das makro funktioniert, wenn ich den bereich auf 14000 zeilen einschränke => dabei bleibt doch die Anzahl der Zeichen gleich hoch.
Die sache mit den ereignisroutinen habe ich leider nicht verstanden. Kannst du mir das etwas genauer erklären?
danke,gruß,
nicole
die sache mit den zeichen scheint zunächst plausibel, allerdings funktiioniert die formel, wenn ich sie manuell einkopiere und das makro funktioniert, wenn ich den bereich auf 14000 zeilen einschränke => dabei bleibt doch die Anzahl der Zeichen gleich hoch.
Die sache mit den ereignisroutinen habe ich leider nicht verstanden. Kannst du mir das etwas genauer erklären?
danke,gruß,
nicole
Antwort 9 von nighty vom 26.11.2020, 16:47 Options
hi nicole :-)
statt die berechnungen ueber formeln durchzufuehren,berechne diese daten mit vb
um zu wissen welche ereignisse wie z.b.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
muesste man den aufbau deiner tabelle kennen und was berechnet werden sollte
gruss nighty
statt die berechnungen ueber formeln durchzufuehren,berechne diese daten mit vb
um zu wissen welche ereignisse wie z.b.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
muesste man den aufbau deiner tabelle kennen und was berechnet werden sollte
gruss nighty
Antwort 10 von nighty vom 26.11.2020, 16:51 Options
hi all :-)
oder die formeln verkuerzen,falls es gehen sollte :-))
gruss nighty
oder die formeln verkuerzen,falls es gehen sollte :-))
gruss nighty
Antwort 11 von rainberg vom 26.11.2020, 16:53 Options
Hallo nighty,
ich weiß nicht, ob es hinsichtlich der Formellänge in VBA andere Limits gibt als in Excel.
In normalen Excelberechnungen jedenfalls, darf eine Formel 1024 Zeichen haben.
@ Nicole
nach Deiner letzten Aussage scheint es doch ein Recoursenproblem zu sein.
Gruß
Rainer
ich weiß nicht, ob es hinsichtlich der Formellänge in VBA andere Limits gibt als in Excel.
In normalen Excelberechnungen jedenfalls, darf eine Formel 1024 Zeichen haben.
@ Nicole
nach Deiner letzten Aussage scheint es doch ein Recoursenproblem zu sein.
Gruß
Rainer
Antwort 12 von Nicole2 vom 26.11.2020, 16:55 Options
Hi nighty,
hab die formeln schon so gut wie möglich verkürzt.
die berechnungen über vb auszuführen, traue ich mir nicht zu. Wir sprechen da in etwa von 100 Kennzahlen, die unterschiedlich berechnet werden.
Gruß,
nicole
hab die formeln schon so gut wie möglich verkürzt.
die berechnungen über vb auszuführen, traue ich mir nicht zu. Wir sprechen da in etwa von 100 Kennzahlen, die unterschiedlich berechnet werden.
Gruß,
nicole
Antwort 13 von nighty vom 26.11.2020, 17:04 Options
hi rainer :-)
ich lass bei microsoft ein wenig und stiess immer wieder auf das limit von 255
was darueber waere, kann muss aber nicht,es zu mannigfalten problemen kommen koennte,hab mir nur einige auszuege durchgelesen
zitat
ich weiß nicht, ob es hinsichtlich der Formellänge in VBA andere Limits gibt als in Excel.
ich meinte vb ohne formeleinsatz
gruss nighty
ich lass bei microsoft ein wenig und stiess immer wieder auf das limit von 255
was darueber waere, kann muss aber nicht,es zu mannigfalten problemen kommen koennte,hab mir nur einige auszuege durchgelesen
zitat
ich weiß nicht, ob es hinsichtlich der Formellänge in VBA andere Limits gibt als in Excel.
ich meinte vb ohne formeleinsatz
gruss nighty
Antwort 14 von nighty vom 26.11.2020, 17:06 Options
hi all :-)
waren nur so meine spontanen ideen :-)
gruss nighty
waren nur so meine spontanen ideen :-)
gruss nighty
Antwort 15 von nighty vom 26.11.2020, 17:08 Options
hi all :-)
wollte mich sonst nicht einmischen oder vordraengeln :-)
gruss nighty
wollte mich sonst nicht einmischen oder vordraengeln :-)
gruss nighty
Antwort 16 von finger59 vom 26.11.2020, 17:14 Options
Hi Nicole,
ich hatte ein ähnliches Problem mit einer Matrixformel - bei mir war bereits nach rund 1.000 Zeilen Schluß mit lustig.
Habe mein Problem dadurch gelöst, daß ich die erste Datenzeile die Formel eingetragen habe.
Dann einen Button angelegt, der die nächsten 500 Zeilen runterkopiert und dann mit kopieren und Inhalte einfügen - Werte und Formate nur noch die Ergebnisse reingestellt.
Danach kommen die nächsten 500 Zeilen dran.
Da Du ja von rund 14000 Zeilen sprichst, brauchst Du das Ganze ja nur 4x durchlaufen lassen, was natürlich auch ein wenig Zeit kostet, aber der Speicher bleibt dafür später nur noch mit einer Zeile mit einer Formel und 64999 Zeilen mit Werten - auch die Dateispeichergröße wird es Dir danken ;-).
Bin mir sicher das die richtigen Excelexperten noch bessere Lösungen haben - aber vielleicht schon mal ein Gedankenansatz.
In diesem Sinne.... have a nice Day... Gruß Helmut
ich hatte ein ähnliches Problem mit einer Matrixformel - bei mir war bereits nach rund 1.000 Zeilen Schluß mit lustig.
Habe mein Problem dadurch gelöst, daß ich die erste Datenzeile die Formel eingetragen habe.
Dann einen Button angelegt, der die nächsten 500 Zeilen runterkopiert und dann mit kopieren und Inhalte einfügen - Werte und Formate nur noch die Ergebnisse reingestellt.
Danach kommen die nächsten 500 Zeilen dran.
Da Du ja von rund 14000 Zeilen sprichst, brauchst Du das Ganze ja nur 4x durchlaufen lassen, was natürlich auch ein wenig Zeit kostet, aber der Speicher bleibt dafür später nur noch mit einer Zeile mit einer Formel und 64999 Zeilen mit Werten - auch die Dateispeichergröße wird es Dir danken ;-).
Bin mir sicher das die richtigen Excelexperten noch bessere Lösungen haben - aber vielleicht schon mal ein Gedankenansatz.
In diesem Sinne.... have a nice Day... Gruß Helmut
Antwort 17 von rainberg vom 26.11.2020, 17:51 Options
Hallo zusammen,
in den Limits für Formellänge, wird zu diesem Thema folgendes gesagt:
Alles weitere findet ihr hier
http://www.xlam.ch/xlimits/index.htm
Gruß
Rainer
in den Limits für Formellänge, wird zu diesem Thema folgendes gesagt:
Zitat:
Excel 97: 1'024 Zeichen
Excel 98 (Mac): 1'024 Zeichen
Excel 2000: 1'024 Zeichen
Excel 2001 (Mac): 1'024 Zeichen
Excel 2002: 1'024 Zeichen
Excel X (Mac): 1'024 Zeichen
Excel 2003: 1'024 Zeichen
Excel 2004 (Mac): 1'024 Zeichen
Pocket Excel 1.0: 255 Zeichen
Pocket Excel 2.0: 255 Zeichen (Inoffiziell)
Excel 2007: 8'192 Zeichen (8 mal mehr als die anderen Excel-Versionen)
Die von Microsoft angegebenen Spezifikation von 1'024 Zeichen gilt nur für Formeln, die in Zellen eingetragen sind. Die in einer bedingten Formatierung, einer Gültigkeitsprüfung oder in einem Namen verwendeten Formeln können maximal 255 Zeichen lang sein (siehe Limitation-ID 173 und Limitation-ID 174).
Bitte beachten Sie, dass eine Matrixformel, die mit VBA einer Zelle zugewiesen wird, wegen eines Bugs höchstens 255 Zeichen lang sein darf. Auch das Abfragen einer Matrixformel mit VBA funktioniert nicht korrekt.
Die Begrenzung von 255 Zeichen für Formeln in Pocket Excel ist insofern ein Problem, wenn Sie eine Arbeitsmappe auf einem Windows-Computer erstellen und dann auf einen Pocket PC/Handheld PC übertragen, um die Mappe in Pocket Excel zu bearbeiten.
Siehe auch Limitation-ID 194.
Weitere Informationen siehe Artikel xllimit4 'Maximale Länge von Formeln'.
Informationen über die besonderen Limitationen von Arrays und Matrixformeln finden Sie auf der Seite Array-Limitationen.
Informationen über die Limitationen und Spezifikationen von Microsoft Excel 2007 finden Sie auf der Seite Limitationen in Microsoft Excel 2007.
Excel 97: 1'024 Zeichen
Excel 98 (Mac): 1'024 Zeichen
Excel 2000: 1'024 Zeichen
Excel 2001 (Mac): 1'024 Zeichen
Excel 2002: 1'024 Zeichen
Excel X (Mac): 1'024 Zeichen
Excel 2003: 1'024 Zeichen
Excel 2004 (Mac): 1'024 Zeichen
Pocket Excel 1.0: 255 Zeichen
Pocket Excel 2.0: 255 Zeichen (Inoffiziell)
Excel 2007: 8'192 Zeichen (8 mal mehr als die anderen Excel-Versionen)
Die von Microsoft angegebenen Spezifikation von 1'024 Zeichen gilt nur für Formeln, die in Zellen eingetragen sind. Die in einer bedingten Formatierung, einer Gültigkeitsprüfung oder in einem Namen verwendeten Formeln können maximal 255 Zeichen lang sein (siehe Limitation-ID 173 und Limitation-ID 174).
Bitte beachten Sie, dass eine Matrixformel, die mit VBA einer Zelle zugewiesen wird, wegen eines Bugs höchstens 255 Zeichen lang sein darf. Auch das Abfragen einer Matrixformel mit VBA funktioniert nicht korrekt.
Die Begrenzung von 255 Zeichen für Formeln in Pocket Excel ist insofern ein Problem, wenn Sie eine Arbeitsmappe auf einem Windows-Computer erstellen und dann auf einen Pocket PC/Handheld PC übertragen, um die Mappe in Pocket Excel zu bearbeiten.
Siehe auch Limitation-ID 194.
Weitere Informationen siehe Artikel xllimit4 'Maximale Länge von Formeln'.
Informationen über die besonderen Limitationen von Arrays und Matrixformeln finden Sie auf der Seite Array-Limitationen.
Informationen über die Limitationen und Spezifikationen von Microsoft Excel 2007 finden Sie auf der Seite Limitationen in Microsoft Excel 2007.
Alles weitere findet ihr hier
http://www.xlam.ch/xlimits/index.htm
Gruß
Rainer
Antwort 18 von Nicole2 vom 27.11.2020, 10:42 Options
Hallo Helmut,
super, der Trick hilft mir ungemein. So werde ich es jetzt mal machen.
danke, grüße
nicole
super, der Trick hilft mir ungemein. So werde ich es jetzt mal machen.
danke, grüße
nicole
Ähnliche Themen
Formeln Excel
MathiG 13.06.2007 - 184 Hits - 2 Antworten
mwst formeln
bendag 20.08.2007 - 34 Hits - 1 Antwort
Ausstieg Makro + allgem. Makro-Frage
Tomschi 28.08.2007 - 51 Hits - 2 Antworten
Formeln
Bobbele 25.09.2007 - 36 Hits - 4 Antworten
Formeln unterdrücken
nettchen 05.12.2007 - 124 Hits - 2 Antworten
Hinweis
Diese Frage ist schon etwas älter, Sie können daher nicht mehr auf sie antworten. Sollte Ihre Frage noch nicht gelöst sein, stellen Sie einfach eine neue Frage im Forum..
Neue Einträge
