Office: (Office 2013) Formelbezüge ändern sich nach Kopieren mit VBA nicht

Hallo liebes Forum,
So langsam bringt mich Excel um den Verstand, vieleicht könnt Ihr mir weiterhelfen...


Ich Kopiere per button mit VBA 4 Source Zeilen (Eine Auftragserfassung und Abgangsmengenbuchung) jeweils direkt unter den Header meines Sheets. Das klappt auch alles 1A..

Die neu kopierten Zellen passen den Zellbezug in der Formel an, soweit wäre meine Welt in Ordnung, nun folgt aber, was ich nicht verstehe:
Wenn ich nun erneut einen "Auftrag löse", dann wird keine der nach unten verschobenen Formeln weiter aktualisiert, stattdessen zielen immernoch alle Bereiche auf die gleiche Zellen, wie vor dem kopieren & einfügen per VBA. nur das nun eine weiterer Auftrag auf die Zellen bezug nimmt..

Füge ich jedoch beliebe Zeilen von Hand im Excel ein so passen sich alle Formeln immer brav an. Auch in den per VBA erstellten Zeilen.

Die per VBA kopierten Zeilen Verhalten sich wie absolute Bezüge, wenn per VBA weitere Zellen eingefügt werden.
Ich verstehe einfach nicht warum dies nur mit dem Kopieren per VBA passiert, das Script ist doch nichts anderes, als wenn ich Zellen per Copy&Paste manuell einfüge?? Und da ginge es ja auch..

Warum passen sich die Formeln beim VBA nicht in der Zeilennummer an?



zu kopierende Formel: =WENNFEHLER($C6-SUMME($H6:$O9);"")

VBA Code:

Code:

Sub Makro1()
    Range("q6:af9").Select
    Selection.Copy  
    Range("A10").Select
    Selection.Insert Shift:=xlDown
    Range("A10").Select
    Application.CutCopyMode = False
End Sub

 

Hi Lutz,

versuche doch mal folgendes: füge zuerst die Zeilen ein und kopiere die Formel anschließend.

---

 

Hallo,

du weißt aber schon, dass sich Excel unterschiedlich verhält, je nach dem, ob eine Formel kopiert wird, oder ob sie von einer Zelle zu einer anderen verschoben wird.
1) Wird eine Formel kopiert, sodass sie dann in einer 2. Zelle danach vorhanden ist, werden die relativen Zellbezüge relativ zur neuen Formelposition angepasst (egal ob manuell oder per VBA).
2) Wird eine Formel verschoben, sodass sie danach nur mehr am neuen Ort enthalten ist, so passt sich die Formel überhaupt nicht an, egal ob es sich um relative oder absolute Bezüge handelt (egal ob manuell oder per VBA).

Genau darin liegt dein Irrtum. Es handelt sich bei deinem Makro nämlich nicht um ein Makro, das lediglich ein Copy&Paste ausführt, sondern das (dreierlei) macht:
1) Es kopiert die ursprüngliche Quellformel aus dem Bereich ab Q6 (Methode: Copy) in die Zwischenablage.
2) Es führt ein 4-zeiliges Verschieben ab Zelle A10 nach unten aus, auch der dort enthaltenen Formeln (Methode: Insert)
3) Es fügt die ursprüngliche Quellformel (aus der Zwischenablage) an der neuen Position ab A10 ein (Methode: Insert)
Somit ist Punkt 1) zusammen mit Punkt 3) ein Copy&Paste - die relativen Bezüge werden relativ zur neuen Position angepasst.
Jedoch stellt Punkt 2) ein Verschieben dar - die verschobene Formel bleibt auch am neuen Ort die gleiche, was ja sehr sinnvoll ist: Sie soll ja auch dort noch auf dieselben Zellen zugreifen.

 

Hallo Beverly,
Hallo Anton,
Besten Dank für promten Antworten & die Inputs.

@Anton,
Also wenn Ich Dir jetzt gerade folgen konnte, heisst dass, ich müsste alle Formeln jedesmal in jedes benötigte Feld neu hineinkopieren, da es sonst immer nur ein Formel verschieben ist, und damit der Zellbezug Absolut bleibt?


Oder ich muss erst die zellen erstellen & Zellen Verschieben, und dann die Formel einfügen, wie Beverly vorgeschlagen hat, damit dies mit den absoluten Bezüge Funktioniert?



Steht mir grad einer auf dem Schlauch...

Gruss Und Danke
Andreas Lutz

 

Hi Andreas,

wenn ich das richtig verstanden habe, werden immer 4 Zeilen eingefügt und die Formel soll sich dann auf diese 4 neu eingefügten Zeilen beziehen?
Ich würde es generell anders machen:

1. Einfügen der Zeilen
2. Formel in die jeweils erste Zeile des neuen Bereichs per VBA eintragen
3. Zellen um 3 Zeilen nach unten ausfüllen und das nur für die Formel

Dabei ist die Frage: steht in jeder Spalte eine Formel? Und wenn ja, welche in welcher Spalte?

---

 

Salü Beverly,
Richtig, es soll jeweils ein neuer Auftrag à 4 Zeilen erstellt werden.
Die Aufträge darunter dürfen sich dann logischerweise nicht auf die neu erstellten Zeilen beziehen, sondern auf die Verschobenen.

Aktuell bleiben die Verweise der Formel jedoch immer auf die gleichen Zellen, da sich die Formeln nicht beim verschieben der zellen anpassen.




Die Formel steht nur in den vier Verbundenen Zellen der Spalte P "Restmenge"

Die 4 Zeilen enthalten jeweils einen der vier Kunden für dieses Produkt sowie die einzelnen Bezugsmengen pro Bezug. Deshalb dann auch "SUMME($H6:$O9" (Alle Bezüge) in Relation zur Herstellmenge (C6), so das wir rechtzeitig in der Produktion einen erneuten Bedarf auslösen können.



Werde Ich mal gemäss deinen Vorschlag umstellen und schauen was passiert.

Gruss
Andreas

 

Hi Andreas,

wenn die Formel nur in der verbundenen Zelle steht, dann reicht es, wenn du sie nur in diese Zelle einträgst - musst halt nur die Zeilennummern darin entsprechend anpassen.

---

 

Hallo,

du hast 2 Arten von Formeln, die von Excel verschieden behandelt werden:
1) Die Formeln, die bereits ab Zelle A10 (und folgende) enthalten sind: Diese werden durch dein Makro hinunterverschoben. Solche verschobenen Formeln ändern sich überhaupt nicht (egal ob relativ oder absolut adressiert).
2) Die Formeln, die ab Zelle Q6 (und folgende) enthalten sind: Diese werden durch dein Makro von Zelle Q6 (und folgende) nach Zelle A10 (und folgende) kopiert. Solche kopierten Formeln werden beim Kopieren angepasst, d.h. die relative Adressierung wird angepasst. Zelladressteile, die kein $-Zeichen enthalten, werden relativ zur neuen Zellposition angepasst.

Dieses Verhalten zeigt Excel generell - egal ob bei manueller Bedienung oder ob per VBA.

Im 1. Schritt:
Wenn du erreichen willst, dass die Formeln, die schon immer ab Zelle A10 (und folgende) enthalten sind, beim "Hinunterverschieben" um 4 Zeilen auch ihre relativen Zelladressen anpassen, sodass sie hinterher nicht wieder auf die ursprünglichen Zellen zugreifen, sondern auf die Zellen, die vermutlich 4 Zeilen unterhalb liegen, so darfst du kein Verschieben (bzw. Zeileneinfügen) im Sinne von Excel ausführen, sondern ein Kopieren des alten Zellbereichs in einen 4 Zeilen tiefer liegenden Zellbereich.
Im 2. Schritt:
Hier führst du das Kopieren der Zellen ab Q6 (und folgende) nach Zelle A10 (und folgende) aus, wobei dieser Punkt ja offenbar gemäß deinen Erwartungen bisher funktioniert hat.

Ich habe dir folgendes Makro2-SUB erstellt, die du anstelle oder zusätzlich zu deinem Makro1 einfügen kannst (Einfach blauen Text markieren, kopieren und unterhalb deines Makros wieder einfügen):

Code:

Sub Makro2()
   'Gemäß Punkt 1: Copy&Paste
 
   'Ermittle letzte benutzt Zeilennummer der Spalte A:
   Dim lztZeile As Long
   lztZeile = Cells(Cells.Rows.Count, 1).End(xlUp).Row 'Nr.der letzten benutzten Zelle in Spalte A
 
   'Kopiere ganze Zeilen ab Zeile 10 und füge sie 4 Zeilen unterhalb wieder ein
   Range("10:" & lztZeile).Copy Destination:=Range("A14")
 
   'Gemäß Punkt 2: Copy&Paste
 
   'Kopiere den Bereich Q6:AF9 nach Zelle A10 (und folgende)
   Range("Q6:AF9").Copy Destination:=Range("A10")
 
   'Schalte Kopiermodus aus
   Application.CutCopyMode = False

End Sub

Es führt genau die beiden obigen von mir geschilderten Schritte aus.

EDIT: Ich habe erst jetzt das Posting mit Beverly gesehen und dass du so etwas "Kriminelles" wie verbundene Zellen hast. Da wird möglicherweise mein Makro scheitern.

 

Hallo Anton,
Besten Dank für die Mühe.

Nun Habe ich aber in den 4 neu erstellten Zeilen jeweils verbunde Zellen:
Einerseits die Chargen und Fa-Nr und auch die Brechungsformel steckt in einer verbundenen Zelle

Das klappt dann mit dem einzelnen Zeilen kopieren nicht.

 

Hi Andreas,

so sollte es funktionieren:

Code:

Sub FormelEinfugen()
    Dim lngStart As Long
    lngStart = 10 '<== diese Zeilennummer kann variabel, muss jedoch immer die 1. Zeile der verbundenen Zelle sein
    Range(Cells(lngStart, 1), Cells(lngStart + 3, 1)).EntireRow.Insert Shift:=xlDown
    Range(Cells(6, 1), Cells(9, 16)).Copy
    Cells(lngStart, 1).PasteSpecial Paste:=xlFormats
    Cells(lngStart, 16).Formula = "=IFERROR($C" & lngStart & "-SUM($H$" & lngStart & ":$O$" & lngStart + 3 & "),"""")"
End Sub

---

 

Super,
vielen Dank, so hauts nun endlich hin!

Gruss Andreas