Gelöst Excel: Formeln werden beim Zeilen einfügen nicht richtig übernommen.

Dieser Thread ist Teil einer Diskussion zu einem Artikel:  Zum News-Artikel gehen

Rosenauer

Mitglied
Grüzi
Betrifft Excel mit MS-Office Professional Plus 2019 (Windows10)
Ich habe eine Betriebskostenabrechnung von einem Auto.
Am Anfang eines Jahres weiss ich noch nicht wie viele Zeilen ich für ein Jahr brauche und habe bis jetzt bei Bedarf neue Zeilen eingefügt und (etwas unprofessionell) die Formeln nachgeführt.

Ich habe aber erst kürzlich gelesen, wenn man in Excel in einer Tabelle eine neue Zeile einfügt, dass die Formeln von der Vorzeile übernommen werden.
Mit CTRL+T habe ich dann jeden Teilbereich eines Jahres als Tabelle definiert und diese mit Tabelle2021, Tabelle2022, usw. bezeichnet.
'(Namensdefinition: Nicht der Sheet-Name, sondern der Tabellenname)

Ich habe aber ein Problem, wenn ich eine Zeile einfüge, werden die Formeln nicht richtig übernommen.
Fürs Forum habe ich eine Arbeitsmappe erstellt, die nur das Wesentliche zum Erklären und ausprobieren beinhaltet.

In Spalte "C" habe ich die Formel, die die gefahrenen Km ausrechnet.
In Zeile 4 "= B4-B3"
In Zeile 5 "= B5-B4"
In Zeile 6 "= B6-B5"
In Zeile 7 "= B7-B6" usw.

Ich markiere Zeile 6 und füge eine neue Zeile ein.
Danach stehen folgende Formeln in der Spalte "C"

In Zeile 4 "= B4-B3"
In Zeile 5 "= B5-B4"
In Zeile 6 "= B6-B5" <-- In der neuen Zeile ist die Formel richtig
In Zeile 7 "= B7-B5" <-- hier Falsch (Sollte B7-B6 sein)
In Zeile 8 "= B8-B7"

Wenn ich eine Zeile mit falscher Formel lösche, dann gibt es Fehler im "#BEZUG"
In Spalte D ist noch eine Formel, dessen Bezüge sich in der gleichen Zeile befinden. Diese Formeln werden korrekt übernommen.

Ich habe nebenbei noch festgestellt, wenn die Formeln schon in den Zellen stehen, wenn man die Tabelle erzeugt (CTRL+T), dann ist das Format z.B. "=B4-B3"
Wenn ich aber die Formeln nach dem Erstellen der Tabelle einfüge, dann bekommt die Formel das Format " =[@Kilometerstand]-B3". Aber auch da ist das gleiche Problem wie oben.

Ist das ein Bug von Excel oder mache ich etwas falsch?
Oder, wie kann ich das Problem beheben?
Vielen Dank für Eure Hilfe.
Gruss Rosenauer

Fuer Forum.JPG
 

Turakos

Stammgast
Grüezi Rosenauer
In der Anlage findest du eine Idee, auf der linken Seite des Tabellenblattes die Erfassung und daneben eine mögliche Auswertung mit einer Pivot-Tabelle.
Je Datensatz eine Zeile (ein Record) nicht nach Jahren getrennt, bei einem neuen Ereignis unten eine Zeile anfügen und die Formel nach unten ziehen.

Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.
Freundliche Grüsse Turakos
 

Anhänge

  • Fahrstrecke.zip
    20,9 KB · Aufrufe: 2

Rosenauer

Mitglied
Vielen Dank für die Antwort.
Ich möchte eigentlich bei meiner Darstellung bleiben.
Siehe unten das Foto vom Original.

Wenn es wirklich nichts gibt, den Fehler zu beheben, hätte ich eine Möglichkeit (Kompromiss) den Fehler zu umgehen. Mit einer VBA-Prozedur könnte ich die erste Zeile mit den Formeln in die unteren Zeilen kopieren.
Die SUB habe ich schon gemacht und funktioniert auch.
Ich müsste noch einen Weg finden, dass diese SUB aufgerufen wird, wenn ich eine Zeile einfüge, aber nur dann (nicht bei jeder Änderung einer Zelle).
Hier
Private Sub Worksheet_Change(ByVal Target As Range)
wäre eine Möglichkeit das Kopieren einzubauen bzw. aufzurufen.
Diese SUB wird aber nach jedem Ändern einer Zelle aufgerufen, was sicher nicht sinnvoll wäre. Zudem ruft das Kopieren diese Sub auch wieder auf und das Programm beisst sich somit selber in den Schwanz.

Vielleicht weiss jemand, wie ich in VBA abfangen kann, wenn eine Zeile eingefügt wurde, aber nur dann.
Das wäre mir schon geholfen.
Vielen Dank schon mal.
Gruss Rosenauer

Fuer Forum_02.JPG
 

nochEinAndreas

Stammgast
Hallo Rosenauer,

ein VBA-Test, ob eine oder mehrere Zeilen eingefügt wurden, könnte so aussehen:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim adr As Variant
    Dim z1 As Long
    Dim z2 As Long
    
    adr = Split(Target.Address, "$")
    z1 = Val(adr(1))
    z2 = Val(adr(2))
    If z1 > 0 Then MsgBox "Zeile(n) eingefügt"
End Sub

Gruß, Andreas
 

Charlito

Stammgast
Markiere Zellen 4 bis 7 und nach unter ziehen
So kann Excel richtig interpretieren
 
Zuletzt bearbeitet:

Rosenauer

Mitglied
Hallo Andreas
Dein Vorschlag ist Super
Habe das eingebaut und getestet.
Es funktioniert einwandfrei.
Es funktioniert auch, wenn ich eine Zeile lösche, da es auch beim Löschen einer Zeile einen Fehler gab. (Fehler im "#BEZUG")

Ich Habe jetzt folgenden Code eingebaut.
Angepasst an die Mustertabelle vom ersten Post.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim adr As Variant
    Dim z1 As Long
    Dim z2 As Long
    
    adr = Split(Target.Address, "$")
    z1 = Val(adr(1))
    z2 = Val(adr(2))
    If z1 > 0 Then
        'MsgBox "Zeile(n) eingefügt"
        Call FormelKopieren
    End If
End Sub

Sub FormelKopieren()
    Dim rngWork As Range
    Dim Breit As Single
    Dim Hoch As Single
    Dim JahresTabelle As String
    Dim T As Integer

    For T = 2021 To 2023
        JahresTabelle = "Tabelle" & CStr(T) 'Tabelle bestimmen
        Set rngWork = Range(JahresTabelle)
        Hoch = rngWork.Rows.Count       'Ist die Höhe der Tabelle ohne die Titelzeile
        Breit = rngWork.Columns.Count   'Ist die Breite
        rngWork.Range(Cells(2, 2), Cells(2, 3)).Select
        Selection.AutoFill Destination:=rngWork.Range(Cells(2, 2), Cells(Hoch, Breit)), Type:=xlFillDefault
    Next
    
End Sub


Trotzdem wäre es noch interessant zu wissen, ob das wirklich ein Bug im Excel ist.
Nur in der Zeile unterhalb der eingefügten Zeile tritt der Fehler auf. Alle weiteren darunter stimmen ja wieder.

@Chartito
Dein Vorschlag habe ich versucht, bin aber nicht ganz z'rank gekommen damit.
Andererseits hat man ja auch seine Gewohnheiten.
Wenn man eine Zeile einfügen muss, dann klickt man eben auf "Zeile einfügen" (wie immer).
Mit dem eingebauten Code funktioniert es ja jetzt bzw. wird der Fehler umgangen.


Vielen Dank all alle.
Das Forum ist ja wirklich Super.
Gruss Rosenauer
 

nochEinAndreas

Stammgast
hallo Rosenauer,

ich hab festgestellt, dass mein Code noch ein paar Macken hat. Er behauptet z.B. auch dass Zeilen eingefügt wurden, wenn
  • ganze Zeilen gelöscht wurden
  • der Inhalt ganzer Zeilen wo anders hin kopiert wurde
  • der Inhalt ganzer Zeilen gelöscht wurde.
ich muss an dem Code noch einiges verbessern. Dann melde ich mich noch mal.

Grüße und ein schönes Wochenende,
Andreas
 

Charlito

Stammgast
Andererseits hat man ja auch seine Gewohnheiten
Du hat Recht, das ist eine Gewohnheiten Sache
Aber Zeile einfügen oder löschen funktioniert nicht immer und mit meiner Methode: Markieren 3-4 Zellen und nach unter ziehen geht schneller und Excel kann richtig interpretieren
 

Anhänge

  • Oldtimer.jpg
    Oldtimer.jpg
    193 KB · Aufrufe: 5

nochEinAndreas

Stammgast
Hallo Rosenauer,

hier meine verbesserte Version des Makros. Ich habe jetzt hoffentlich die meisten Szenarien von Einfügen/Löschen/Verändern von Zeilen/Zellen abgefangen. Bis auf 2, bei denen es mir nicht gelungen ist. Die kann man aber hoffentlich verschmerzen:
  • Unterhalb der letzten gefüllte Zeile werden leere(!) Zeilen eingefügt. Das erkennt der Makro nicht. Aber warum sollte man das auch tun?
  • Die Datei wird frisch geöffnet. Es sind schon ganze Zeilen ausgewählt. Wenn diese Zeilen jetzt gelöscht werden, ohne dass vorher irgend etwas anderes geschieht, wird das fälschlicherweise als Zeilen einfügen erkannt. Aber das kommt hoffentlich sehr selten vor.
Der Code ist jetzt um einiges länglicher geworden. Füge ihn in den Codebereich des entsprechende Blattes ein.

Gruß und noch einen schönen Sonntag,
Andreas


Code:
Option Explicit

' Globale Variable. Nr. der letzten gefüllten Zeile
Dim letzte As Long

' Wenn Das Blatt ausgewählt wird, die letzte gefüllte Zeile ermitteln:
Private Sub Worksheet_Activate()
    ' Aus irgendwelchen Gründen wird beim Aufruf von SpecialCells
    ' das SelectionChange Ereignis ausgelöst.
    ' Um dies zu vermeiden, setzen wir:
    Application.EnableEvents = False
    
    ' Die Zeile der letzten gefüllten Zelle ermitteln:
    letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    ' Die Verarbeitung von Ereignissen wieder zulassen:
    Application.EnableEvents = True
End Sub

' Um die letzte gefüllte Zeile möglichst aktuell zu halten,
' wird sie bei jedem Asuwahl-Wechsel ermittelt.
' Auch hier wieder umschiffen des SpecialCells-Phänomens.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    Application.EnableEvents = True
End Sub

' Wenn Zeilen/Zellen eingefügt oder gelöscht werden,
' ist dass ein Change-Ereignis.
Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Die Zeilen/Zellen die eingefügt oder gelöscht wurden,
    ' werden an die Funktion 'zeilenEingefügt' übergeben:
    If zeilenEingefügt(Target) Then
    
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Hier dein Code
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        
    End If
End Sub

' Die Funktion 'zeilenEingefügt' ermittelt, ob Zeilen eingefügt wurden
' und gibt WAHR oder FALSCH zurück:
Function zeilenEingefügt(bereich As Range) As Boolean
    Dim adrs As String
    Dim adr As Variant
    
    ' Die Adresse/n des Bereichs, der geändert wurde:
    adrs = Replace(bereich.Address, ":", "")
    
    ' Diese Adresse/n werden in Einzelteile zerlegt.
    ' Der Trenner ist das $.
    adr = Split(adrs, "$")
    
    ' Wenn der Erste Teil der Adresse/n eine Zahl ist (ein Zeile),
    ' wurden ganze Zeilen, eingefügt, gelöscht oder
    ' deren Inhalt verändert:
    zeilenEingefügt = IsNumeric(adr(1))
    
    ' Wieder das Phänomen mit SpecialCells umschiffen:
    Application.EnableEvents = False
    
    ' Wenn Zeilen eingefügt wurden,
    ' ist die letzte gefüllte Zeile jetzt weiter unten als vorher.
    ' Dies wird duch 'And' kombiniert mit der vorherigen Abfrage,
    ' ob ganze Zeilen beeinflusst wurden:
    zeilenEingefügt = zeilenEingefügt And (ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row > letzte)
    
    ' Die 'neue' letzte Zeile wird ermittelt:
    letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    ' Ereignis-Behandlung wieder einschalten:
    Application.EnableEvents = True
End Function
 

Rosenauer

Mitglied
Hallo Andreas
Vielen Dank.
Ich habe Deinen Vorschlag eingebaut und ausprobiert.
Beim Einfügen einer Zeile funktioniert es tadellos.
Hingegen sollte das Formelkopieren auch durchlaufen, wenn eine Zeile gelöscht wird, weil es dann Fehler im "#BEZUG" ergab.

Ich konnte das beheben indem ich in der

Function zeilenEingefügt(bereich As Range) As Boolean

Die Zeile

zeilenEingefügt = zeilenEingefügt And (ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row > letzte)

geändert habe in

zeilenEingefügt = zeilenEingefügt And (ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row <> letzte)

Ich habe nur das ">" in "<>" geändert.

Auch habe ich noch nach der Zeile
Call FormelKopieren
Die Zeile:
Target.Select 'Zeile selektieren wo Zeile eingefügt wurde
eingefügt.
Das ist noch etwas Finish, damit nach Procedurende nicht die letzte Zeile selektiert bleibt, sondern diese, die man eingefügt bzw. gelöscht hat.

Ich denke, dass der Thread nun erledigt ist.
Der Fehler selbst besteht immer noch.
Die Umgehung dessen funktioniert nun dank Eurer Hilfe tadellos.

Vielen Dank an Alle die mitgeholfen haben.

Gruss Rosenauer
 

nochEinAndreas

Stammgast
Nachtrag:
Den ursprüngliche Fehler haben schon etliche Leute im Netz moniert. Selbst bei MS ist er bekannt:
Aber dort scheint man sich einen Sch... darum zu kümmern.

Schönen Abend,
Andreas
 
Oben