Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel in Zelle aktualisieren

Formel in Zelle aktualisieren
Georg
Guten Abend Leute!
Ich schreibe in eine Zelle die Summen-Formel b4 bis z.B. B9 - funktioniert.
Wenn ich nun eine gesamte Zeile zB. 5 herausschneide und unten einfüge, dann stimmt die Formel in der Zelle nicht mehr.
Sub test()   '--Sinngemäß--
Zeilenb = 3
letztezeile = wks1.Cells(Rows.Count, 1).End(xlUp).Row
Set wks = ThisWorkbook.Sheets("Tabelle1")
wks.Cells(Zeilenb - 1, 2).Formula = "=sum(b4:b" & Zeilenb - 4 & ")"
wks.Rows(i).Cut
wks.Rows(letztezeile + 1).Insert shift:=xlDown
End Sub

Wie kann diese mit VBA angepasst werden?
Danke im Voraus.

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

Betreff
Benutzer
Anzeige
AW: Formel in Zelle aktualisieren
21.10.2009 19:51:29
Daniel
Hi
dein Makro ist so aber sowieso nicht funktionsfähig, den die Variablen wks1 und i haben keinen Wert.
Auch die Formel kann nicht funktioniern, den Zeilenb ist 3 und Zeilenb-4 wird -1, damit wird die Formel:
"=sum(b4:b" & Zeilenb - 4 & ")" zu "=sum(b4:b-1")" was aber keinen Sinn macht, da es keine negativen Zeilennummern gibt, zumindest nicht in Excel 2002, (wie das in Excel 2007 aussieht, weiß ich nicht.)
was deine Frage angeht, es ist völlig normal und von Excel so gewollt, daß die Formeln angepasst werden, wenn Zeilen ausgeschnitten oder eingefügt werden.
dh wenn du die Formel =Summe(B4:B9) hast und die Zeile 5 ausschneidest, dann wird die Formel automatisch abgeändert auf =Summe(B4:B8).
das ist auch in 99% der Anwendungsfälle das, was der Anwender braucht.
Sollte der Zellbezug hingegen immer fix B4:B9 sein, dann muss man die Formel anders schreiben und den Zellbezug über BEREICH.VERSCHIEBEN oder INDIREKT oder INDEX definieren.
in deinem Fall z.B =Summe(Indirekt("B4:B9"))
im Makro dann so:

....Formula = "=SUM(INDIRECT(""B4:B" & Zeilenb - 4  &"""))

Gruß, Daniel
Anzeige
Die ZellFormel muss nicht aktualisiert wdn,...
21.10.2009 20:00:21
Luc:-?
...Georg,
die passt sich selber an, wenn du die ausgeschnittene Zeile vor der letzten Summenzeile einfügst (ggf immer eine stets letzte Leerzeile mitsummieren). Falls du aber deine Subroutine meinst, da passt sich natürlich nichts automatisch an. Deshalb sucht sie ja auch nach dem letzten Summanden, wobei mir völlig unklar ist, was mit Zeilenb - 4 für eine Bereichsadresse herauskommen soll, wenn stets Zeilenb=3 ist... :->
Außerdem könntest du diese Subroutine niemals in deine Personl.xls oder ein AddIn auslagern, weil sie dann wg ThisWorkbook nicht mehr fktn würde. Die allgemeine Form für die Verwendung der aktuellen Mappe ist ActiveWorkbook, aber den Unterschied scheinen ja selbst einige „vbCracks" nicht zu kennen...
Gruß Luc :-?
Anzeige
AW: Die ZellFormel muss nicht aktualisiert wdn,...
21.10.2009 20:20:32
Georg
Erstmal Danke für die Antworten.
Zeilenb ist nur für diesen Test fix.
Die Zeile wks.Cells(Zeilenb - 1, 2).Formula = "=sum(b4:b" & Zeilenb - 4 & ")"
fügt die Formel richtig ein.
Folgendes steht an ganz anderer Stelle:
wks.Rows(i).Cut
wks.Rows(letztezeile + 1).Insert shift:=xlDown
Mein Wunsch wäre es natürlich, das sich die Formel selbst anpasst, und aus =Summe(B4:B9) dann =Summe(B4:B8) wird. nur leider bleibt in der Zelle trotzdem B4:B9 stehen.
Ich dachte mir, da fehlt noch was?
AW: Formel in Zelle aktualisieren
21.10.2009 21:54:23
Georg
Hallo nochmal,
Jetzt hab ich endlich herausgefunden, was meinen Fehler verursacht.
Nur hab ich leider keine Lösung dafür.
Und zwar sobald ich die letzte Zeile vor der Summenformel ausschneide, dann bekomme ich eine Zirkelbezugswarnung.
Wie kann ich das umgehen?
Anzeige
AW: Formel in Zelle aktualisieren
21.10.2009 21:59:27
Daniel
Hi
lade doch mal die Datei mit dem Beispiel hoch, das würde vieles vereinfachen.
Im prinzip würde ich es versuchen zu vermeiden, erst ne Formel reinzuschreiben und dann danach Zeilen aus dem Bezugsbereich der Formel auszuschneiden.
Kannst du nicht einfach erst die ganzen Zeilenumstellungen durchführen und am Schluss die Formeln einfügen, wenn die Tabellenstruktur mal vollständig aufgebaut ist?
Gruß, Daniel
AW: Formel in Zelle aktualisieren
21.10.2009 22:09:53
Georg
Hallo Daniel,
Meine Mappe ist sehr komplex. Aber mein Problem liegt im Grundsatz, das ich eine Zeile vor der Summe diese nicht ausschneiden kann. Das hat jetzt nicht mal mehr mit VBA etwas zu tun, weiß aber nicht wie ich das umgehen kann.
Ein Beispiel:
in Zelle A5 steht die Formel =Summe(A1:A4)
nun möchte ich die ganze Zeile 4 ausschneiden, und in Zeile 6 einfügen. (Zeile 1,2 und 3 kein Problem)
verstehe das voll nicht, warum das mit Zeile 4 nicht funktioniert?
Anzeige
AW: Formel in Zelle aktualisieren
21.10.2009 22:28:57
Daniel
Excel versucht ja, beim Ausschneiden die Zellbezüge neu zu berechnen und muss erahnen, was der Anwender machen will.
Mit dieser Vermutung kann Excel aber auch daneben liegen.
Wenn du jetzt die Zellen 1-3 ausschneidest, ist eigentlich das naheliegenste, daß sich der Summenbereich verkleinert.
schneidest dagegen die Zeile 4 aus, dann ist das die unterste Zelle im Summenbereich und Excel geht in diesem Fall davon aus, daß der Anwender den Summenbereich nicht verkleinern, sondern verschieben willst und die eingefügte Zelle in Zeile 6 die neue untergrenze der Summe ist. Weil allerdings die Summe in der gleichen Spalte steht, und du die Zeile unter die Summe kopierst, entsteht dann ein Zirkelbezug.
vermeiden kannst du das Problem auf 2 Arten.
1. du arbeitest nicht mit AUSSCHNEIDEN - EINFÜGEN, sondern mit KOPIEREN - EINFÜGEN und LÖSCHEN.
Das funktioniert, weil Excel beim Kopieren nicht davon ausgeht, daß der Summenbereich verschoben werden soll.
2. du vermeidest den Zellbezug auf A4 in der Summenformel und arbeitest mit folgender Formel:
=Summe(B1:Bereich.Verschieben(B5;-1;0))
dann ist die untere Grenze des Summenbereichs immer die Zelle ein über B5, egal was du löschst oder einfügst.
Gruß, Daniel
Anzeige
Lösung
21.10.2009 23:01:21
Georg
Hallo,
habe das ganze jetzt so gelöst.
wks4.Rows(i).Copy
wks4.Rows(letztezeile + 1).Insert shift:=xlDown
wks4.Rows(i).Delete
Gruß Georg
AW: Lösung
21.10.2009 23:44:41
Daniel
Das war ja auch mein Vorschlag.
Gruß, Daniel
AW: Lösung
22.10.2009 14:56:13
Georg
Hallo Daniel.
Danke nochmal.

342 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige