12
Formeln um eine Zeile runterziehen wenn
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)
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)
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 82472075785
Url: https://administrator.de/contentid/82472075785
Ausgedruckt am: 25.11.2024 um 08:11 Uhr
12 Kommentare
Neuester Kommentar
Geht das?
In den Editor als neues Modul dann mit Strg+F11 usw ;)
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 SubIn den Editor als neues Modul dann mit Strg+F11 usw ;)
Moin,
kannst du nicht in die Zelle, in der die Formeln enthalten sein sollen ein
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?
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.
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
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.
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 SubIch 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
Geht auch, dann musst du nicht immer manuell mit strg F11 starten
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 
:
:
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 SubHallo 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"
Hallo Kartin,
Dankeschön für die Idee, die ich gerade getestet habe:
Leider werden schon beim reinkopieren die beiden Zeilen
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.
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:=xlFillDefaultWas 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.
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.
Hi nochmal.
Kannst Du diese Version einmal testen?
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
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)
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)
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.
@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.
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 👋
2
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)
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)
