jokurt
Nov 09, 2023
view1106
view12
0
Goto Top

Formeln um eine Zeile runterziehen wenn

GermanQuestionDevelopmentVB for Applications
Guten Morgen,
abgesehen davon, dass in der Beispieldatei die Formel in M nichts anzeigt*, geht es mir um folgendes:

In Spalte A steht ein Datum, in M, N, O und R stehen Formeln.
Nun möchte ich erreichen, dass immer wenn in Spalte A ein neuer Datensatz eingegeben wird, die Formeln aus der Zeile darüber runtergezogen werden bzw wieder gelöscht wenn auch das Datum gelöscht wird.

Leider hatte ich mit VBA schon seit einigen Jahren nichts mehr zu tun, so dass mir jeder Lösungsansatz fehlt.

​Viele Grüße Jokurt (Uwe)

*(Die Formel in M habe ich aus einem anderen Projekt übernommen, wo sie problemlos funktioniert, und aus meiner Sicht auch korrekt angepasst. Von dem Ergebnis hängt auch das korrekte Ergebnis in Spalte O und R ab)
screenshot 2023-11-09 072045
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 82472075785

Url: https://administrator.de/contentid/82472075785

Printed on: April 28, 2024 at 01:04 o'clock

12 Comments
Latest comment
Goto Top
Member: Cleanairs
Cleanairs Nov 09, 2023 at 07:28:08 (UTC)
Goto Top
Geht das?

Sub FormelnRunterziehen()

'Deklarationen  
Dim Zeile As Long
Dim Zelle As Range

'Aktuelle Zeile ermitteln  
Zeile = ActiveCell.Row

'Zelle in Zeile darüber ermitteln  
Zelle = Cells(Zeile - 1, 1)

'Wenn Zelle nicht leer, Formeln runterziehen  
If Zelle.Value <> "" Then  

    For Each Zelle In Range("A:D", Cells(Zeile, 1), Cells(Zeile, 1))  
        Zelle.Value = Cells(Zeile - 1, Zelle.Column).Value
    Next Zelle

End If

End Sub

In den Editor als neues Modul dann mit Strg+F11 usw ;)
Member: em-pie
em-pie Nov 09, 2023 at 07:34:02 (UTC)
Goto Top
Moin,

kannst du nicht in die Zelle, in der die Formeln enthalten sein sollen ein =WENN($A2<>""; SUMMENPRODUKT(...);"") machen? oder soll Excel dann auch die Zellbezüge innerhalb der Formel anpassen?
Mitglied: 8030021182
8030021182 Nov 09, 2023 updated at 11:44:51 (UTC)
Goto Top
Hallo
Code nicht in ein Modul, sondern in das entsprechende Sheet einfügen (weil Event-Prozedur des Sheets benutzt wird).
Code wird automatisch ausgeführt sobald In Spalte A Eintragungen gemacht werden.

23aa08786a63d08f869cde552cf4faa7

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim cell As Range, rng1 As Range, rng2 As Range
    If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then  
        Application.EnableEvents = False
        For Each cell In Target
            Set rng1 = Range(Cells(cell.Row, "M"), Cells(cell.Row, "O")).Offset(-1, 0)  
            Set rng2 = Cells(cell.Row, "R").Offset(-1, 0)  
            If cell.Value <> "" Then  
                Set rng1 = Range(Cells(cell.Row, "M"), Cells(cell.Row, "O")).Offset(-1, 0)  
                Set rng2 = Cells(cell.Row, "R").Offset(-1, 0)  
                rng1.AutoFill Destination:=rng1.Resize(2, rng1.Columns.Count), Type:=xlFillDefault
                rng2.AutoFill Destination:=rng2.Resize(2, rng2.Columns.Count), Type:=xlFillDefault
            Else
                rng1.Clear
                rng2.Clear
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Ich hätte die Formeln allerdings einfach schon diverse Zellen runtergezogen und diese in ein WENNFEHLER() oder WENN() gekapselt und leeren Text anzeigen lassen wenn nichts in Zelle A steht, dann brauchst du auch kein Makro

Gruß Katrin
Member: Cleanairs
Cleanairs Nov 09, 2023 at 09:53:37 (UTC)
Goto Top
Geht auch, dann musst du nicht immer manuell mit strg F11 starten
Mitglied: 8030021182
8030021182 Nov 09, 2023 updated at 11:52:03 (UTC)
Goto Top
Wenn er die Tabelle als intelligente Tabelle formatieren würde, würden die Formeln auch automatisch von Excel hinzugefügt und entfernt wenn man neue Zeilen hinzufügt oder entfernt, ganz ohne Skript face-smile:

screenshot
Member: Jokurt
Jokurt Nov 09, 2023 at 17:03:25 (UTC)
Goto Top
Zitat von @Cleanairs:

Private Sub Worksheet_Change(ByVal Target As Range)

'Deklarationen  
Dim Zeile As Long
Dim Zelle As Range

'Aktuelle Zeile ermitteln  
Zeile = ActiveCell.Row

'Zelle in Zeile darüber ermitteln  
Zelle = Cells(Zeile - 1, 1)

'Wenn Zelle nicht leer, Formeln runterziehen  
If Zelle.Value <> "" Then  

    For Each Zelle In Range("A:D", Cells(Zeile, 1), Cells(Zeile, 1))  
        Zelle.Value = Cells(Zeile - 1, Zelle.Column).Value
    Next Zelle

End If

End Sub

Hallo cleanairs,
erstmal Danke für die Idee
Ich hab sie gerade wie hier abgebildet ausprobiert (als Worksheet_Change "im" Tabellenblatt) bekomme jedoch die Fehlermeldung: "Falsche Anzahl an Argumenten oder ungültige Zuweisung zu einer Eigenschaft"
Member: Jokurt
Jokurt Nov 09, 2023 at 17:24:49 (UTC)
Goto Top
Hallo Kartin,
Dankeschön für die Idee, die ich gerade getestet habe:
Leider werden schon beim reinkopieren die beiden Zeilen
rng1.AutoFill Destination:=rng1.Resize(2, $rng1.Columns.Count), Type:=xlFillDefault
rng2.AutoFill Destination:=rng2.Resize(2, $rng2.Columns.Count), Type:=xlFillDefault
rot markiert.
Was genau an den beiden Zeilen nicht stimmt kann ich nicht nachvollziehen.

Deine andere Idee in WENNFEHLER() gekapselt und runterziehen hab ich im Originalprojekt.
Für M, O und R geht das auf den ersten Blick gut, auch der "leere" Text.
Bei der Formel N (Fehlzeitprüfung) funktioniert das leider nicht.
Wenn bei kompletten Datensätzen ein 0 steht ist das i.O., soll auch so sein, jedoch wird beim runterziehen immer ein 0 angezeigt auch wenn der Rest der Zeile komplett leer ist.
Ein weiterer unerwünschter Effekt hatte ich bei der Sortierung (VBA Code) nach Enddatum. Da wurden dann die vermeidlich leeren Zellen nach oben gesetzt.
Daher kam ich letztlich auf die Idee auf das runterziehen zu verzichten und das hoffentlich wie Eingangs beschrieben lösen zu können.

Grüße, Uwe

P.S. Möglicherweise hab ich was übersehen\falsch gemacht oder es liegt an meiner Version (2016) aber die "intelligente Tabelle" hat mir die Zeile nach den Überschriften "zerschossen" obgleich ich den Datenbereich korekt angegeben habe.
Mitglied: 8030021182
8030021182 Nov 09, 2023 updated at 21:00:43 (UTC)
Goto Top
Leider werden schon beim reinkopieren die beiden Zeilen rot markiert
Ach herjehmineh, mein Fehler, habe ihn oben korrigiert, kommt davon wenn man PowerShell und VBA gleichzeitig schreibt 😀, die Dollarzeichen vor den Variablen gehören da natürlich nicht hin.
Member: Cleanairs
Cleanairs Nov 10, 2023 at 09:20:51 (UTC)
Goto Top
Hi nochmal.

Kannst Du diese Version einmal testen?

Private Sub Worksheet_Change(ByVal Target As Range)

'Deklarationen  
Dim Zeile As Long
Dim Zelle As Range

'Aktuelle Zeile ermitteln  
Zeile = ActiveCell.Row

'Wenn Zelle in Spalte A nicht leer, Formeln runterziehen  
If Cells(Zeile, 1).Value <> "" Then  

    For Each Zelle In Range(Cells(Zeile, "M"), Cells(Zeile + 1, "R"))  
        Zelle.Value = Cells(Zeile - 1, Zelle.Column).Value
    Next Zelle

End If

End Sub
Member: Jokurt
Jokurt Nov 14, 2023 at 06:55:00 (UTC)
Goto Top
Guten Morgen Cleanairs,
Guten Morgen Katrin,

abgesehen davon, dass ich zwischenzeitlich die Tabelle in eine Intelligente umgewandelt habe, habe ich beide Codes implementiert (natürlich angepasst) und getestet.
Leider zeigen beide Codes, insbesondere beim löschen einer Zeile ein unerwartetes Verhalten.
@Cleanairs: Beim Löschen in B und H gerät der Code in eine Dauerschleife.
@Katrin: Bei der Eingabe eines neuen TN (Spalte B) werden die Formeln in T:V korrekt runtergezogen, Y leider nicht.
Lösche ich in B den TN Namen, werden in der darüberliegenden Zeile in T:V und Y die Einträge gelöscht, nicht aber die komplette Zeile in der ich den Namen gelöscht habe.
Vielleicht mag jemand nochmal drüber schauen.
Ich hab die XLSM auf mein Onedrive gelegt.

[Modulplanung_Demo] https://1drv.ms/x/s!AiRU4jLWcKsAjx09Enlv6uaimYVp?e=ISXhYz

Perfekt wäre, wenn bei der Gelegenheit auch in I und P "x" gesetzt werden, da dies ohnehin Standard ist und somit alle Formeln direkt funktionieren.

Viele Grüße, JoKurt (Uwe)
Mitglied: 8030021182
8030021182 Nov 14, 2023 updated at 07:13:22 (UTC)
Goto Top
Zitat von @Jokurt:
@Katrin: Bei der Eingabe eines neuen TN (Spalte B) werden die Formeln in T:V korrekt runtergezogen, Y leider nicht.
Lösche ich in B den TN Namen, werden in der darüberliegenden Zeile in T:V und Y die Einträge gelöscht, nicht aber die komplette Zeile in der ich den Namen gelöscht habe.
Vielleicht mag jemand nochmal drüber schauen.

Tja, kein Wunder , ich zitiere dich aus der Frage

In Spalte A steht ein Datum, in M, N, O und R stehen Formeln.
Nun möchte ich erreichen, dass immer wenn in Spalte A ein neuer Datensatz eingegeben wird, die Formeln aus der Zeile darüber runtergezogen werden bzw wieder gelöscht wenn auch das Datum gelöscht wird.

Genau darauf wurde der Code kreiert, klappt hier auch im Test wie gewünsch. Du hast es wohl selbst falsch angepasst.

Code lesen und verstehen und erst dann selbst für deine Zwecke anpassen, sonst hast du von der ganzen Sache rein gar nichts gelernt! Und wer will sich schon von einem Forum abhängig machen ...?! It's your work, not ours. We delivered the template, you do the rest! So start using the round thing on your neck, this is not a "make me my work" service 👋
heart2
Member: Jokurt
Jokurt Nov 14, 2023 at 10:18:36 (UTC)
Goto Top
Hallo Katrin,
herzlichen Dank für Deinen Kommentar.
Wäre ich so tief in der Materie wie Du und viele andere, hätte ich die Frage heute früh erst gar gestellt, sondern den "Job selbst gemacht" - ja, ist auch meiner!
Das Beispiel auf den u.a. Dein Code fußt habe ich mit Absicht so einfach (und so nah) wie möglich gewählt, eben um daraus zu lernen.
Nochmals vielen Dank für Deinen Support bis hierher.
Grüße, JoKurt (Uwe)
GermanQuestionDevelopmentVB for Applications
Hotly discussed
admtechDeveloper diary: Release 6.1admtechDaniEnd of availability for classic Teams clientDani