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

Helfe beim Thema Formelbezüge ändern sich nach Kopieren mit VBA nicht in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von a. Lutz, 12. Mai 2022.

  1. a. Lutz Neuer User

    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
    Formelbezüge ändern sich nach Kopieren mit VBA nicht Excel.JPG
     
    a. Lutz, 12. Mai 2022
    #1
  2. Beverly
    Beverly Erfahrener User
    Hi Lutz,

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


    Formelbezüge ändern sich nach Kopieren mit VBA nicht GrußformelFormelbezüge ändern sich nach Kopieren mit VBA nicht Beverly's Excel - Inn
     
  3. Exl121150 Erfahrener User
    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.
     
  4. a. Lutz Neuer User

    Formelbezüge ändern sich nach Kopieren mit VBA nicht

    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
     
  5. Beverly
    Beverly Erfahrener User
    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?



    Formelbezüge ändern sich nach Kopieren mit VBA nicht GrußformelFormelbezüge ändern sich nach Kopieren mit VBA nicht Beverly's Excel - Inn
     
  6. a. Lutz Neuer User
    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
     
  7. Beverly
    Beverly Erfahrener User
    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.



    Formelbezüge ändern sich nach Kopieren mit VBA nicht GrußformelFormelbezüge ändern sich nach Kopieren mit VBA nicht Beverly's Excel - Inn
     
  8. Exl121150 Erfahrener User

    Formelbezüge ändern sich nach Kopieren mit VBA nicht

    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.
     
    Zuletzt bearbeitet: 13. Mai 2022 um 15:49 Uhr
  9. a. Lutz Neuer User
    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.
     
  10. Beverly
    Beverly Erfahrener User
    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
    


    Formelbezüge ändern sich nach Kopieren mit VBA nicht GrußformelFormelbezüge ändern sich nach Kopieren mit VBA nicht Beverly's Excel - Inn
     
    1 Person gefällt das.
  11. a. Lutz Neuer User
    Super,
    vielen Dank, so hauts nun endlich hin!

    Gruss Andreas
     
Thema:

Formelbezüge ändern sich nach Kopieren mit VBA nicht

Die Seite wird geladen...
  1. Formelbezüge ändern sich nach Kopieren mit VBA nicht - Similar Threads - Formelbezüge ändern Kopieren

  2. Zellenfarbe nach aktueller Uhrzeit ändern

    in Microsoft Excel Hilfe
    Zellenfarbe nach aktueller Uhrzeit ändern: Hallo Leute, die Zellenfarbe soll sich ändern, wenn die aktuelle Uhrzeit ist. Im Wochenkalender von Mo-So sind untereinander für jeden einzelnen Tag die Uhrzeiten von 0:00 bis 23:45 in 15...
  3. Status ändern nach Bearbeitung

    in Microsoft Access Hilfe
    Status ändern nach Bearbeitung: Hallo Accessprofis*:)* Ich als völlig unwissender was Access betrifft habe da mal eine Frage zu einer Accessdatenbank. [ATTACH] Es geht um die Spalte Status in der Datenbank. 2 sachen sind mir...
  4. formelbezug?

    in Microsoft Excel Hilfe
    formelbezug?: liebes forum, ich habe ein problem. wenn ich in eine zelle diese formel eingebe, dann erhalte ich die korrekte anzahl wert in diesem bereich (29)....
  5. Wir haben ein problem bei mindestens einem Formelbezug in dieer Arbeistmappe festgest

    in Microsoft Excel Hilfe
    Wir haben ein problem bei mindestens einem Formelbezug in dieer Arbeistmappe festgest: Hallo miteinander, ich kann mir das nicht erklären warum ich diesen Fehler bekomme: Bild In meiner Excel Datei ist eine Arbeitsmappe und auf dieser habe ich ganze 30 Formeln stehen. die 30...
  6. Excel 2007 - Problem mit Formelreferenzen

    in Microsoft Excel Hilfe
    Excel 2007 - Problem mit Formelreferenzen: Sehr geehrte Forumsmitglieder, ich habe ein Problem mit Formelreferenzen. Nachfolgend erst einmal die Formel, die ich verwende:...
  7. dynamischer Formelbezug in Pivottabellen

    in Microsoft Excel Hilfe
    dynamischer Formelbezug in Pivottabellen: Hallo, ich bräuchte mal wieder die Hilfe der versierten Excelnutzer. Ich möchte die Formel in der unteren bunten Tabelle, mit einem eindeutigen Bezug zur oberen Pivottabelle herstellen....
  8. Probleme beim ausführen eines Makros mit Formelbezug

    in Microsoft Excel Hilfe
    Probleme beim ausführen eines Makros mit Formelbezug: Und mal wieder ne Frage... Folgende Formel.. If Not Intersect(Range("R7:R96"), Target) Is Nothing Then If Target = "x" Then MsgBox "Achtung! Sie haben einen falschen Wert im Abgabetermin!"...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden