Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
928to932
928to932
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Zeile in and. Blatt finden, Zellen mit Formeln kop

Zeile in and. Blatt finden, Zellen mit Formeln kop
21.11.2007 14:30:12
Peter
Hallo alle zusammen,
mein Code kann bis jetzt die im Blatt "Cashflow" markierte Zeile löschen und dann nachsehen in welchen Spalten der Tabelle Formeln stehen und dann ab der Zeile über der gelöschten Zeile die Formeln nach unten hin kopieren (damit die Bezüge wieder stimmen).
Im Blatt "Cashflow" stehen in Spalte 1 Nummern und im Blatt "MwSt" auch in Spalte 1.
Mein Code soll dann noch die Zeile mit der selben Nummer in Spalte 1 im Blatt "MwSt" finden, diese dann löschen und auch nachsehen in welchen Spalten Formeln stehen und diese dann nach unten hin kopieren (wie im Blatt "Cashflow")
Das Zeile finden und löschen funktioniert schon, nur das Formeln suchen und kopieren bekomme ich nicht hin.
Kann mir jemand helfen?

Private Sub Zeile_loeschen_Click()
Dim cell As Range, lngAb As Long, lngAnz As Long
SpeedUp True
ActiveSheet.Unprotect Password:="Peter"
If ActiveCell.Row  7 Then
If ActiveCell.Column = 1 Then 'Zelle in Spalte A aktiviert
If MsgBox("Wollen Sie diese Zeile loeschen?", vbOKCancel + vbQuestion, _
"Achtung!") = 1 Then
'Zeile mit der selben Nummer im Blatt "MwSt" finden, löschen und Formeln  _
kopieren
If Worksheets("Cashflow").Name = ActiveSheet.Name Then
Worksheets("MwSt").Unprotect Password:="Peter"
With Worksheets("MwSt")
On Error Resume Next
.Rows(.Columns(1).Find(ActiveCell.Value, lookat:=xlWhole, LookIn:=xlValues). _
Row).Delete
On Error GoTo 0
'hier ist wohl der Fehler, man kann hier nicht lngAb verwenden, was dann?
lngAb = ActiveCell.Row
lngAnz = Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next cell
End With
End If
Worksheets("MwSt").Protect Password:="Peter"
'markierte Zeile im Blatt "Cashflow wird gelöscht und Formeln kopieren
Rows(ActiveCell.Row).Delete
'ActiveCell.EntireRow.Delete
'lngAb = ActiveCell.Row
lngAnz = Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next cell
End If
Else
MsgBox "Sie haben keine Zeile markiert!"
ActiveSheet.Protect Password:="Peter"
Exit Sub
End If
Else
MsgBox "Sie können diese Zeile nicht loeschen!"
ActiveSheet.Protect Password:="Peter"
Exit Sub
End If
ActiveSheet.Protect Password:="Peter"
SpeedUp False
End Sub


Grüße aus Berlin

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Zeile finden, Formeln kopieren
23.11.2007 17:49:05
Peter
Hallo,
ich versuche mal mein Problem zu vereinfachen.
Was ich schon kann ist mit dem folgenden Code eine bestimmte Zeile finden.
With Worksheets("MwSt")
On Error Resume Next
.Rows(.Columns(1).Find(ActiveCell.Value, lookat:=xlWhole, LookIn:=xlValues).Row).Delete
On Error GoTo 0
End With
Nun möchte ich, ab der Zeile die über der gelöschten Zeile war, die Zellen bis ans Ende der Tabelle (mit Ende meine ich bis in die Zeile in der die letzten Eintragungen sind) kopieren, aber nur die Zellen die Formeln enthalten (um die Bezüge zu aktualisieren).
Der Code müßte, wie schon gesagt, in folgende Richtung gehen. Er funktioniert so aber noch nicht.
Ich weiß nicht wie ich sage: kopiere ab der Zeile über der gelöschten Zeile.
Hinweis: das Blatt ist nicht aktiv, der Code wird aus einem anderen Blatt gestartet und in Blatt "MwSt" ist keine Zeile markiert!
Vielleicht kann mir jetzt jemand helfen?
For Each cell In Rows("keine Ahnung" - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next cell
Grüße aus dem nass-kalten Berlin

Anzeige
AW: Zeile finden, Formeln kopieren
23.11.2007 19:41:43
ingUR
Hallo, Peter,
vielleicht wäre es einfacher, wenn Du zusätzlich noich eine Beispielfassung Deiner Arbeitsmappe hochladen könntest, denn das Nachbauen der Umgebung könnte dazu führen, dass mann am falschen Problem sich versucht.
Aber soweit ich das bis hierher verstanden habe, gelingt es Dir nicht den Bezug auf die "Mwst"-Tabelle für das Kopieren herzustellen, denn Dein Pseudocode

For Each cell In Rows("keine Ahnung" - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next cell

spricht ja mit Rows die Zeilen des aktiven Blattes an. Warum kannst Du für den Kopiervorgang nicht auf das Mwst-Blatt wechseln und anschließend wieder zurück?
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln kop
23.11.2007 20:09:00
ingUR
Hallo, Peter,
ob mit dieser einfachen Arbeitsmappe, in der sich die beiden Blätter ChasFlow, Mwst und Dein Programmcode aus dem Eröffnungsbeitrag (Protect-Anwiesungen und SpeedUp-Aufrufe sind auskommentiert) befinden, die Problematik beschreiben werden kann, vermag ich nicht zu sagen, denn zumindest das Löschen einer markierten Zeile funktioniert auf beiden Blättern (bitte im Editor Private-Funktion ausführen lassen; die Numerierung in der Spalte C dient zum Überblick, dass tatsächslich eine Zeile in beiden Blättern entfernt wurde.).
Einzig die Erweiterung des Bereiches der CashFlow-Daten ist ohne kenntnis des Zusammenhangs für mich nicht ohne weiteres begründbr.
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
24.11.2007 20:16:00
Peter
Hallo Uwe,
ich lade mal meine Datei hoch.
Der Code befindet sich in Modul 2. Gestartet wird der Code über die Schaltfläche "loeschen" auf meiner eigenen Symbolleiste. Wenn du dann das Löschen testest wirst du mein Problem schnell erkennen und bestimmt auch lösen können.
https://www.herber.de/bbs/user/48015.zip
Grüße aus Berlin

AW: Zeile in and. Blatt finden, Zellen mit Formeln
25.11.2007 14:19:00
ingUR
Hallo, Peter,
ohne dass ich jetzt im Einzelnen den Programmcode nachvollzogen habe, scheint mir in der Hochgeladenen Arbeitsmappe in der Prozedur "Zeile_loeschen_Click" m Bereich der FOR-Schleuife, die sich auf das Kopieren der Formel bezieht, die Referenzierung zur Tabelle "Mwst" nicht korrrekt zu sein; es fehlt ein Punkt vor Rows(lngAb-1), da sich dieser Teil ja auf deas Blatt "Mwst" beziehen soll:
For Each cell In .Rows(lngAb - 1).Spec ....

With Worksheets("MwSt")
On Error Resume Next
.Rows(.Columns(1).Find(ActiveCell.Value, lookat:=xlWhole, LookIn:=xlValues). _
Row).Delete
On Error GoTo 0
'hier ist wohl der Fehler, man kann hier nicht lngAb verwenden, was dann?
lngAb = ActiveCell.Row
lngAnz = Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In .Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next cell
End With
End If

Ich hoffe das war's.
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
26.11.2007 12:34:30
Peter
Hallo Uwe,
das war's noch nicht.
Jetzt kommt die Fehlermeldung: Unzureichender oder nicht ausreichend defenierter Verweis!
Das mit dem Punkt kanns nicht sein, dann müsste der Teil des Codes der sich auf das Blatt "Cashflow" bezieht auch nicht funktionieren und das tut er. Ich verwende im Moment für das Blatt "Cashflow" und auch für das Blatt "MwSt" lngAb. Für das Blatt "MwSt" kann ich das dann wohl so nicht machen, (wäre doppelt, keine aktive Zeile etc.) hab ich im Archiv gelesen. Für das Blatt "MwSt" muss man eine andere Lösung finden und ich hab keine Ahnung welche!!! Hast du eine Code-Idee, die du auch gleich einbauen kannst?
Danke
Gruß
Peter

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
27.11.2007 13:52:00
ingUR
Hallo, 'Peter,
da die Beitragskette bereits im Archiv gelandet ist, komme ich erst jetzt dazu, Deineantwort zu entdecken, wo ich die Emailberachrichtigungen durchsehe.
Nun kann es sein, dass ich irgendowo in Deiner Fragestellung etwas nicht richtig beachtet habe, denn ich meinte, dass es der Fehler war, dass das Löschen der korrospondierenden Zeile in der Tabelle "Mwst" wohl funktionierte, nicht jedoch ders anschließndee Fornelübertrag um den durch das Zeilenlöschen entstanden falschen #BEZUG zu korregieren.
In Deiner hochgeladenen Datei konnte ich Erreichen, dass durch das Setzen des Referenzpunktes, der die ROWS mit dem in dem With-Block gesetztem Objekt (hier die Wabelle "Mwst") verbindet, die Formeln des Tabellenblattes "MwsT" ersetzt werden. Werde ich später geren nochmals überprüfen.
Wenn nun die möglichkeit besteht, dass in der VashFlow" und deer "Mwst"-Tabelle unterschidliche Bereichgrö9en auftreten könne, da nicht jeder "ChasFlow".Zeile an der gleichen Stelle eine "Mwst"-Zeile zugeordnet ist, dann müßte man dieses Problem durch eine gesonderte Varible lösen. Wenn dieses denn wirklich das Problem sein sollte, dann melde Dich bitte noch einmal dazu.
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
27.11.2007 16:46:00
Peter
Hallo Uwe,
genau dieses (unterschiedliche Bereichsgrößen) ist Ursache des Problems. In den älteren Dateiversionen bin ich von Zahlungen in Blatt "Cashflow" und im Blatt "MwSt" ausgegangen die immer in der gleichen Zeile stehen. Einen Code, der mit dieser Annahme funktionierte hatte ich auch. Als ich fetsgestellt hatte, dass die Zeilen auch unterschiedliche sein können, habe ich in Spalte A eine Nummernspalte eingeführt, die jede Zahlung identifiziert. Im With Block der "MwSt" sieht man auch das dort nach den gleichen Werten in Spalte 1 gesucht wird. Deshalb funktioniert auch das löschen der gleichen Zahlung mit der selben Nummer. Nur das Formeln kopieren, wegen der Bezüge, funktioniert nicht im Blatt "MwSt". Wie gesagt, einfach mal testen. (Zeile in "Cashflow" markieren, loeschen auf Symbolleiste anklicken, OK bestätigen, Ergebnis in beiden Blättern kontrollieren).
Kannst du den Lösungscode einbauen?!
Danke
Gruß
Peter

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
27.11.2007 20:35:00
ingUR
Hallo, Peter,
es kann sein, dass es nun ein wenig kompliziert wird, nicht wegen der VBA-Möglichkeiten zur Lösung der Aufgabe, sondern eher wegen der Möglichkeit, in einem bestehenden Programmcode, der viele Anzeichen aufweist, dass er bereits auch in etlichen Schritten "bedarfsgerecht" zusammengesetzt wurde, Änderungen vorzunehmen, die zu unbeabsichtigten Nebeneffekten führen, deie ich selbst noch nicht überblicken kann.
Zum ersten ist da, dass in der Beispieldatei das Tabellenblatt "CashFlow" frei von Zellenfolrmeln ist, somit das Übertragen der Zellenformel auf in der Spalte darunterligende Zellen nicht erforderlich wird; oder hat die Originalmappe auf dieser Tabelle Formeln?
Zweitens ist mir der Summenteil +2 in der Bereichsweitenformel lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2 nicht klar, was jedoch kein sonderliches Problem aufwirft, da hierdurch eben nur die Formeln um eine Zeile weiter geschrieben wird, als Formeln in der Spalte vorhanden sind und dieser Effekt einfach dadurch eliminiert wird, dass eben als Summand +1 gesetzt werden kann.
Zur Vorgehensweise beim nachfolgendem Test, schlage ich vor, dass Du in einer Beispielmappe
  1. die bestehende Funktion Private Sub Zeile_loeschen_Click() umbenennest in Private Sub Zeile_loeschen_V0_Click(), o.ä. um dann
  2. den nachfolgenden Programmcode für die Prozedur Private Sub Zeile_loeschen_Click() in das gleiche Modul einzufügen.
  3. 
    Private Sub Zeile_loeschen_Click()
    Dim cell As Range
    Dim lngAb As Long, lngAnz As Long
    If Worksheets("Cashflow").Name  ActiveSheet.Name Then
    MsgBox ("Aufruf nur vom Tabellenblatt ChashFlow aus möglich!" & vbLf & "Keine weitere  _
    Aktion!")
    Exit Sub
    End If
    SpeedUp True
    If ActiveCell.Row  7 Then
    If ActiveCell.Column = 1 Then 'Zelle in Spalte A aktiviert
    If MsgBox("Wollen Sie diese Zeile loeschen?", vbOKCancel + vbQuestion, _
    "Achtung!") = 1 Then
    'Zeile mit der selben Nummer im Blatt "MwSt" finden, löschen und Formeln  _
    kopieren
    With Worksheets("MwSt")
    .Unprotect Password:="Peter"
    On Error Resume Next
    lngAb = .Columns(1).Find(ActiveCell.Value, lookat:=xlWhole, LookIn:= _
    xlValues).Row
    .Rows(lngAb).Delete
    On Error GoTo 0
    'hier ist wohl der Fehler, man kann hier nicht lngAb verwenden, was dann?
    lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2
    For Each cell In .Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
    cell.Copy
    cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
    Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
    Transpose:=False
    Next cell
    .Protect Password:="Peter"
    End With
    'markierte Zeile im Blatt "Cashflow wird gelöscht und Formeln kopieren
    With ActiveSheet
    .Unprotect Password:="Peter"
    .Rows(ActiveCell.Row).Delete
    ' ab hier könnte der Programmteil entfallen; s.u.
    lngAb = ActiveCell.Row
    On Error GoTo 0
    lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2
    For Each cell In .Rows(lngAb - 1)
    If cell.HasFormula Then
    With cell
    .Copy
    .Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
    Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:= _
    False, _
    Transpose:=False
    End With
    End If
    Next cell
    ' bis hierher könnte der Programmteil ab dem Beginnhinweis gelöscht werden; s.u.
    .Protect Password:="Peter"
    End With
    End If
    Else
    MsgBox "Sie haben keine Zeile markiert!"
    Exit Sub
    End If
    Else
    MsgBox "Sie können diese Zeile nicht loeschen!"
    Exit Sub
    End If
    SpeedUp False
    End Sub
    

  4. Testen und Prüfung der Wikungen.
Im wesentlichen ist der Programmcode so geblieben, wie er bisher vorgesehen waren, bis auf dass ein paar Umgruppierungen vorgenommen wurden, die jedoch wohl von ihrem Grundgedanken her nachvollziehbar sind.
Der Programmcode kann um den Bereich

lngAb = ActiveCell.Row
On Error GoTo 0
lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In .Rows(lngAb - 1)
If cell.HasFormula Then 'SpecialCells(xlCellTypeFormulas, 23)
cell.Select
With cell
.Copy
.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:= _
False, _
Transpose:=False
End With
End If
Next cell

gekürzt werden, wenn die Tabelle "CashFlow" keine Zellenformeln enthällt, die angepaßt werden müssen.
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
28.11.2007 11:12:53
Peter
Hallo Uwe,
1. das Blatt "Cashflow" hat Formeln, sie sind bei dir wohl auf den ersten Blick nicht zu sehen , weil in den Codes die Blätter immer schreibgeschützt werden. Das Passwort ist "Peter".
2. Da hast du recht.
Unter den Bedingungen nützt mir dein Vorschlag leider nichts.
Hast du noch eine Idee? Der Code muss schon den Bedingungen aus meiner letzten Nachricht folgen!
Gruß
Peter

AW: Zeile in and. Blatt finden, Zellen mit Formeln
28.11.2007 14:34:00
ingUR
Hallo, Peter,
.....
1. das Blatt "Cashflow" hat Formeln, sie sind bei dir wohl auf den ersten Blick nicht zu sehen , weil in den Codes die Blätter immer schreibgeschützt werden. Das Passwort ist "Peter".
....
Nein, das ist wohl nicht der Fall, denn die For Each cell In Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23) bezogen auf die CashFlow-Tabelle führt zum Fehler, der nur dann auftritt, wenn keine Zellenformaln in dem genannten Bereich gefunden werden. Daher die Umwandlung der Abfrage, die im Falle, dass keine Zwellenformel gefundenwird, auch zu keiner weiteren Aktion führt.
....
2. Da hast du recht.
....
Womit habe ich recht? Vermag den Bezug nicht zu erkennen.
...
Unter den Bedingungen nützt mir dein Vorschlag leider nichts.
...
Welche Bedingungen nutzt Dir mein Vorschlag nichts? Hast Du den Code laufen lassen? Und wenn, ja, welche Unzulänglichkeiten hast Du festgestellt?
...
Hast du noch eine Idee? Der Code muss schon den Bedingungen aus meiner letzten Nachricht folgen!
...
Tut er es denn nicht? Und wenn ja, an welchen Punkten (siehe bitte auch vorherige Antwort)? Ggf. können wir auch versuchen, direkt die Probleme zu besprechen, denn momentan fehlt die Zeit, mich länger in die mögliche Gesamtthematik einzuarbeiten.
Gruß,
Uwe

Anzeige
AW: Zeile in and. Blatt finden, Zellen mit Formeln
28.11.2007 15:33:00
Peter
Hallo Uwe,
habs jetzt hinbekommen und zwar folgendermaßen:

Private Sub Zeile_loeschen_Click()
Dim cell As Range
Dim lngAb As Long, lngAnz As Long
SpeedUp True
If ActiveCell.Row  7 Then
If ActiveCell.Column = 1 Then 'Zelle in Spalte A aktiviert
If MsgBox("Wollen Sie diese Zeile loeschen?", vbOKCancel + vbQuestion, _
"Achtung!") = 1 Then
'Zeile mit der selben Nummer im Blatt "MwSt" finden, löschen
With Worksheets("MwSt")
.Unprotect Password:="Peter"
On Error Resume Next
lngAb = .Columns(1).Find(ActiveCell.Value, lookat:=xlWhole, LookIn:= _
xlValues).Row
.Rows(lngAb).Delete
On Error GoTo 0
'Im Blatt "MwSt" Formeln kopieren
lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In .Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,  _
_
Transpose:=False
Next cell
.Protect Password:="Peter"
End With
'markierte Zeile im Blatt "Cashflow löschen
With ActiveSheet
.Unprotect Password:="Peter"
.Rows(ActiveCell.Row).Delete
'Im Blatt "Cashflow" Formeln kopieren
lngAb = ActiveCell.Row
On Error GoTo 0
lngAnz = .Cells(65536, 1).End(xlUp).Row - lngAb + 2
For Each cell In .Rows(lngAb - 1).SpecialCells(xlCellTypeFormulas, 23)
cell.Copy
cell.Offset(1, 0).Resize(lngAnz, 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,  _
_
Transpose:=False
Next cell
.Protect Password:="Peter"
End With
End If
Else
MsgBox "Sie haben keine Zeile markiert!"
Exit Sub
End If
Else
MsgBox "Sie können diese Zeile nicht loeschen!"
Exit Sub
End If
SpeedUp False
End Sub


Danke für deine Hilfe
Gruß
Peter

346 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige