# Zeilen löschen und gleichzeitig Formeln einfügen



## josef24 (29. Juni 2020)

Hallo zusammen. Komme nochmal mit einer Frage zu VBA Code. Ich will einer Spalte eine Formel zuweisen. Gleichzeitig sollen Datensätze (Zeilen) gelöscht werden die einen gewissen Wert übersteigen. Mein Problem ist nun "Formel einfügen" und "Zeile löschen wenn Bedingung erfüllt ist", in Einklang zu bringen. Das mit der Zeile separat  löschen wenn die Bedingung erfüllt ist, funktioniert, wenn ich aber versuche die Formel (egal welche) mitzuberücksichtigen, geht der Code nicht mehr.
Danke für Unterstützung und Gruß Josef

Fehler bei:   
	
	
	



```
If .Cells(lngZeile, zielspalte) >= 10 Then
```
, gelb markiert.  Laufzeitfehler 13, Typen unverträglich

Mein Code:

```
Private Sub Worksheet_Activate() ' Zeileentfernen()
Dim lngZeile As Long
'   Dim i As Long
      zielspalte = 6
        With Info
          lngZeileMax = .Cells(Rows.Count, zielspalte).End(xlUp).Row
        For lngZeile = lngZeileMax To 2 Step -1
    If .Cells(lngZeile, zielspalte) >= 10 Then .Rows(lngZeile).Delete ' Löscht Daten älter 10 Tage
    Next '  lngZeile
   
        ' .Range("F2").FormulaLocal = "=WENN(($E$1:$E$= """";"""";DATEDIF(E2;HEUTE();""D""))"
       
     .Range("F2").FormulaLocal = "=WENN(E2="""";"""";DATEDIF(E2:E;HEUTE();""D""))"
   
        ' Worksheets("TabStat").Range("b21").FormulaLocal = "=MIN((ArbTab!$R$1:$R$" & letztezeile & "))"
       
            ' .Range("F2").FormulaLocal = "=MIN((ArbTab!$R$1:$R$" & letztezeile & "))"

        End With
    Application.CutCopyMode = False '  Beendet den Copiervorgang
End
End Sub
```


----------



## Yaslaw (29. Juni 2020)

.Cells ist ein Range-Objekt

```
If .Cells(lngZeile, zielspalte).Value >= 10 Then
```


----------



## josef24 (29. Juni 2020)

Danke erstmal. Ist noch etwas mehr Unterstützung möglich? Ich komm nicht so recht weiter. Gruß Josef


----------



## Yaslaw (30. Juni 2020)

Was geht den nicht? Das ist doch die Zeile mit dem Fehler? Oder habe ich dein Rext falsch vertanden.


----------



## josef24 (30. Juni 2020)

Hallo, muss so glaube ich, noch etwas ergänzen. Zum einen soll die Zeile gelöscht werden, die größer 10 in Spalte 6(F) ist. Im gleichen wollte ich für die Spalte "F" eine Formel eintragen lassen. Meine Frage ging dahin, ob und wie die beiden Aufgaben im Code vereinbar, also gleichzeitig erfüllt werden können?  Gruß Josef


----------



## Yaslaw (30. Juni 2020)

Ich habe mal in deinem Beitrag den Code-Tag auf VB gestellt.
Sorry, ich kann den Code nicht lesen ohne ihn vorher zu formatieren. DIe Anzahl der Tabulatoren ist kein Zufallsprinzip.

Hier mal dein Code in lesbarer formatierung. Wozu das END in der zweitletzten Ziel. Zudem sind diverse Variabeln nicht deklariert

```
Private Sub Worksheet_Activate() ' Zeileentfernen()
    Dim lngZeile As Long, zielspalte As Long, lngZeileMax As Long
    zielspalte = 6
    With Info
        lngZeileMax = .Cells(Rows.Count, zielspalte).End(xlUp).Row
        For lngZeile = lngZeileMax To 2 Step -1
            If .Cells(lngZeile, zielspalte) >= 10 Then .Rows(lngZeile).Delete ' Löscht Daten älter 10 Tage
        Next
        .Range("F2").FormulaLocal = "=WENN(E2="""";"""";DATEDIF(E2:E;HEUTE();""D""))"
    End With
    Application.CutCopyMode = False '  Beendet den Copiervorgang
    'End !!!!! Dieses End passt nirgends hin.
End Sub
```
Man erkennt, dass du die Zeilen rückwärts durchgehst id ggf löscht. Das ist gut so. Am Ende setzt du die Formel in F2. Ev. sollte dies auch in die Schleife und nur wenn das Feld < als 10 ist?

```
With Info
        For lngZeile = lngZeileMax To 2 Step -1
            If .Cells(lngZeile, zielspalte) >= 10 Then 
                .Rows(lngZeile).Delete ' Löscht Daten älter 10 Tage
            ELse
                .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ":E;HEUTE();""D""))"
            End If
        Next
    End With
```


----------



## josef24 (30. Juni 2020)

Danke für deine Unterstützung. Es funktioniert leider nicht wie erhofft. Weder das Löschen von Zeilen noch das rein Kopieren der Formel ist in Funktion. Anmerkung: Die Formel sollte in die Spalte "F" reinkopiert werden. Gruß Josef

Hab den Code mal so zusammen geführt:

```
Private Sub Worksheet_Activate() ' Zeileentfernen()
Dim lngZeile As Long
   Dim i As Long
      zielspalte = 6
      
       With Info
        For lngZeile = lngZeileMax To 2 Step -1
            If .Range(lngZeile, zielspalte) >= 30 Then
                .Rows(lngZeile).Delete ' Löscht Daten älter 10 Tage
            Else
                .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ":E;HEUTE();""D""))"
            End If
        Next
    End With
 Application.CutCopyMode = False '  Beendet den Copiervorgang
 
End Sub
```


----------



## Zvoni (30. Juni 2020)

Das hier ist falsch:
DATEDIF(E2:E;HEUTE();""D"")

E2:E
Du gibst eine unvollendete Range an, wobei bei der DateDif eh keine MultiCell-Range angegeben wird
Muss wohl eher heissen: DATEDIF(E2:HEUTE();""D"")

Näxtes:
"=WENN(E2="""";"""";DATEDIF(E2:E;HEUTE();""D""))"

Mal das ganze Konstrukt......
E2:E siehe oben.
Du hast da Zellbezüge, welche im Code gar nicht verändert werden. Wieso willst du die Formel per VBA einfügen?
Gib sie direkt im Blatt ein (kann man so übrigens viel besser testen, ob das richtige herauskommt)


----------



## josef24 (30. Juni 2020)

Hallo nochmal. Die Schreibweise der Formel in der Spalte "F" sieht bei mir so aus und macht auch das was ich will. Formel ist: =WENN(E2="";"";DATEDIF(E2;HEUTE();"D")). Da aber ständig Zeilen wegen etwaiger Aktualisierungen  gelöscht bzw. dazu kommen (Es sind mehrere die in der Tabelle arbeiten), wäre es aus meiner Sicht einfacher gewesen die Formel in der Spalte "F" immer aktuell rein zu kopieren. Ohne die gesamte Spalte mit der Formel zu belegen wüste ich keine geeignetere Lösung, wie die Formel immer nach Bedarf per Code einzufügen. Gruß Josef


----------



## Zvoni (30. Juni 2020)

D.h. Du hast in jeder zeile in spalte F diese Formel?


----------



## josef24 (30. Juni 2020)

Ja, so hatte ich es mir vorgestellt. Die Formel jeweils rein kopieren wenn ein Datum in Spalte "E" geschrieben wird. Gruß Josef


----------



## Zvoni (30. Juni 2020)

Dann hast du es ja schon fast in deinem post 7
nur noch das „:E“ rauswerfen


----------



## josef24 (30. Juni 2020)

Danke, dass mit der Formel kopieren  passt perfekt. Leider löscht er aber nicht die Daten/Zeilen wo die Bedingung erfüllt wäre. Könnte man da nochmal schauen? Gruß Josef


----------



## Zvoni (30. Juni 2020)

Weil du dein Lösch-Kriterium eben nicht VORHER drin hast, wenn die Prüfung stattfindet.
Unabhängig von deiner Vorgehensweise, würde ich es eher so machen:
2 Schleifen!
1. Schleife füllt deine Formel in Spalte F
2. Schleife (rückwärts) prüft auf dein Kriterium, falls erfüllt, delete
In der Reihenfolge. Zuerst deine Formel füllen, dann aufräumen


----------



## josef24 (30. Juni 2020)

Danke, die Reihenfolge mit Formel einfügen und Zeilen löschen in gewisser Reihenfolge hatte ich auch in meiner Überlegung. Kann es mangels Kenntnis leider nicht umsetzen. Könnte vielleicht noch jemand helfen? Gruß Josef


----------



## Zvoni (1. Juli 2020)

Aircode! Ungetestet

```
With Info
For lngZeile = lngZeileMax To 2 Step -1
    .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ";HEUTE();""D""))"
Next

For lngZeile = lngZeileMax To 2 Step -1
    If .Range(lngZeile, zielspalte) >= 30 Then .Rows(lngZeile).Delete ' Löscht Daten älter 30 Tage
Next
End With
```


----------



## josef24 (1. Juli 2020)

Danke, der Code läuft, Formel einfügen ist aktiv, löschen ist aber so nicht aktiv. Auch die Funktion löschen als eigenständiger Code macht nichts???? Es gibt in keinster Weise einen Fehlerhinweis. Gruß Josef


----------



## Zvoni (1. Juli 2020)

Arggghh...ich sehe gerade in der Lösche-Routine ist es ".Range(Zeile,Spalte)"
Änder das mal ab auf ".Cells(Zeile,Spalte)"

P.S.: Du kannst auch beide Schleifen zusammenlegen:

```
With Info
For lngZeile = lngZeileMax To 2 Step -1
    .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ";HEUTE();""D""))"
    If .Cells(lngZeile, zielspalte) >= 30 Then .Rows(lngZeile).Delete ' Löscht Daten älter 30 Tage
Next
End With
```
Falls es immer noch nicht will, kann es an der Formatierung der Zelle liegen.
Schau dir mal im Einzelschritt an, ob es als Zahl oder Text angezeigt wird.

EDIT: Ich geh mal schwer davon aus, dass "Info" ein Worksheet-Objekt ist?


----------



## Zvoni (1. Juli 2020)

Ok
muss bis morgen warten


----------



## Zvoni (2. Juli 2020)

Dein "With Info" funktioniert nicht.
Mache ich in deinem Code dagegen ein "With Tabelle1" gehts


----------



## josef24 (2. Juli 2020)

Sorry, aber welcher Code soll es denn sein, bei mir geht jedenfalls nichts. Die Mappe1, da geht aber nichts? Gruß Josef


----------



## Zvoni (2. Juli 2020)

Ersetz mal in deiner Mappe 1 (Anhang) das " With Info" mit "With Tabelle1", und schon gehts.
Habs bei mir eben getestet

EDIT: Ähhh....quatsch. Da fehlt noch was: und zwar die ermittlung der letzten Zeile.
Die muss noch vor die Schleife rein

```
With Tabelle1
lngZeileMax = .Cells(.Rows.Count, zielspalte).End(xlUp).Row
'Schleifen
```


----------



## josef24 (2. Juli 2020)

Danke,  werde mal in der Originalversion starten.


----------



## josef24 (2. Juli 2020)

Ist wohl noch ein Hacken in der Sache,  sorry, es bewegt sich nichts, leider. Frage mich allerdings warum es in der einen Tabelle geht, und in meinem "Original" nicht. Wo könnte ich denn noch Veränderungen vornehmen? In der Spalte 6 wo ich die Formel anwende, geschieht nichts. Ich aktualisiere über eine Eingabemaske. Ist "Worksheet_Activate()" nicht ein automatisches Aktivieren eines Prozesses?  Gruß Josef

Der Code:

```
Private Sub Worksheet_Activate() '   Open() '  Activate()  ' ** Zeilenentfernen() ' Zeileentfernen()

Dim lngZeile As Long, zielspalte As Long  '  , With as variable
        zielspalte = 6
                With Tabelle1
              
             lngZeileMax = .Cells(.Rows.Count, zielspalte).End(xlUp).Row
          
            For lngZeile = lngZeileMax To 2 Step -1

        .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ";HEUTE();""D""))"
  
If .Cells(lngZeile, zielspalte) >= 10 Then .Rows(lngZeile).Delete ' Löscht Daten älter 30 Tage
Next
    End With
Application.CutCopyMode = False '  Beendet den Copiervorgang
End Sub
```


----------



## Zvoni (3. Juli 2020)

josef24 hat gesagt.:


> Ist "Worksheet_Activate()" nicht ein automatisches Aktivieren eines Prozesses?  Gruß Josef


Dann stelle ich die Frage anderst: Wann soll dein Code ausgeführt werden?
Sheet_Activate löst nur aus, wenn du das Sheet wechselst (bzw. beim Startup)


----------



## Zvoni (3. Juli 2020)

Ich glaube ich habe deinen Fehler gefunden:
Die Ermittlung der letzten Zeile findet in Bezug auf Spalte F statt.
Wenn da natürlich keine Einträge sind, wirft er als letzte Zeile natürlich nur schmarrn raus.
Änder mal wie folgt ab:

```
With Tabelle1
        lngZeileMax = .Cells(.Rows.Count, 1).End(xlUp).Row 'Hier Änderung von "zielspalte" auf "1" Spalte A
        For lngZeile = lngZeileMax To 2 Step -1
            .Cells(lngZeile, zielspalte).FormulaLocal = "=WENN(E" & lngZeile & "="""";"""";DATEDIF(E" & lngZeile & ";HEUTE();""D""))"
            If .Cells(lngZeile, zielspalte) >= 10 Then .Rows(lngZeile).Delete ' Löscht Daten älter 10 Tage
        Next
    End With
```
Ich habe jetzt Spalte A genommen. Du musst natürlich selbst wissen, welche Spalte definitiv Einträge bis zum "Ende" hat (Adressen-Spalte?).
Ich habe die If-Delete-Zeile auskommentiert, und alle Formeln wurden korrekt gesetzt


----------



## josef24 (3. Juli 2020)

Danke für deine erneute Hilfe. Habe den Code mit Erfolg getestet. Ist mir für die Praxis eine große Hilfe. Nochmal vielen Dank, Gruß Josef


----------

