Zeilen mit Formeln in Wert ändern

Bild

Betrifft: Zeilen mit Formeln in Wert ändern
von: Reinhard
Geschrieben am: 25.06.2015 14:53:58

Hallo zusammen,
ich suche nach ein einer Lösung für folgendes Problem.
Meine Formeln in Zeilen sind über einen Sverweis an ein Datumsfeld gekoppelt. Die Daten werden jeden Tag (Mo-Fr) für ein tägliches Reporting aktualisiert. Pro Tag habe ich für unterschiedliche Zeiträume insgesamt 46 Zeilen.
Ich würde gerne über VBA die Formeln nach der Aktualisierung für den jeweiligen Tag als Werte festschreiben.
Die WWW Recherche hat viele Ansätze gegeben, aber keine konkrete Lösung ergeben.
Mein Tabellenaufbau sieht folgendermaßen aus:
Spalte A das Datum und in Spalte B den Zeitraum, die weiteren Spalten enthalten die Formeln für die verschiedenen Kennzahlen.
Das jetzt aktuelle Datum fängt in der Zeile 6 an.
Vielen Dank vorab für Eure Hilfe.
Gruß Reinhard

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Michael
Geschrieben am: 25.06.2015 14:58:26
Hi Reinhard,
mit Kopieren und Inhalte Einfügen-Werte - das kennst Du ja mit Excel "gut".
Das läßt sich easy mit dem Makrorekorder aufzeichnen.
Ansonsten recherchiere halt mal: excel vba copy pastespecial
Schöne Grüße,
Michael

Bild

Betrifft: Nachtrag - VBA
von: Michael
Geschrieben am: 25.06.2015 15:08:13
Oder so:

Sub ersetzen()
Dim c As Range
' Selection ist der markierte Bereich,
' also zum Testen erst Bereich markieren,
' dann Makro ausführen
For Each c In Selection: c.Value = c.Value: Next
' ********
' * oder *
' ********
' ggf Selection durch festen Bereich ersetzen:
For Each c In Range("A7:C33"): c.Value = c.Value: Next
End Sub
Schöne Grüße,
Michael

Bild

Betrifft: AW: Nachtrag - VBA
von: Reinhard
Geschrieben am: 25.06.2015 15:36:01
Hallo Michael,
kann man in das Marko die Selektion aufgrund des Datums in der Spalte a einbauen?
Gruß Reinhard

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Reinhard
Geschrieben am: 25.06.2015 15:13:47
Hallo Michael,
ich möchte ergänzen das die Tagesdaten untereinander geschrieben sind und für die zukünftigen Tage die Formeln und das Datum auch bereits in der Tabelle vorhanden sind.
Beispiel:
In Spalte A steht in den
Zeilen 6-51 der 24.06.2015
Zeilen 52-98 der 25.06.2015
Zeilen 99-144 der 26.06.2015
Wenn ich die Tabelle z.B.: heute für den 24.06.2015 aktualisiere soll über das Makro die Zeilen 6-51 als Wert festgeschrieben werden und die Zeilen darunter sollen die Formeln weiter beinhalten.
Also der Bereich wo die Formeln in Werte geändert werden ändert sich von Tag zu Tag.
Gruß Reinhard

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Frank
Geschrieben am: 25.06.2015 17:47:59
Hallo Reinhard,
ich bin zwar nicht Michael, aber der würde sich bestimmt auch mehr über ne Beispieltabelle freuen, als über die länglichen Erklärungen.
Auch wäre es gut, solche Informationen gleich im ersten Posting rüberzubringen.
Zum Selberausprobieren: AktDat=Date liefert Dir das aktuelle Datum, das Du dann mit den Zellinhalten vergleichen kannst. Übrigens auch ein Grund dafür, dass bei Datumsoperationen immer eine Beispieldatei dabei sein sollte: Da kommt's meist auf die Formatierung an.
Grüsse,
Frank

Bild

Betrifft: danke, Gruß owT
von: Michael
Geschrieben am: 25.06.2015 20:15:48


Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Reinhard
Geschrieben am: 26.06.2015 10:39:32
Hallo,
ich habe hier eine Beispieltabelle abgelegt:
https://www.herber.de/bbs/user/98495.xlsx
VG Reinhard

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Frank
Geschrieben am: 26.06.2015 14:16:41
Hallo Reinhard,
Prinzipiell so:

Sub test()
lZ = Sheets(1).Range("A10000").End(xlUp).Row
AktDat = Date
For i = 4 To lZ
    If Sheets(1).Cells(i, 1).Value > AktDat - 1 Then
        For j = 1 To 9
            Sheets(1).Cells(i, j).Copy
            Sheets(1).Cells(i, j).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=  _
_
                False, Transpose:=False
        Next
    End If
Next
End Sub
Grüsse,
Frank

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Michael
Geschrieben am: 26.06.2015 17:00:59
Hi zusammen,
soweit ich das verstanden habe, soll nur für je 1 Tag ersetzt werden, nicht für den Rest der Tabelle.
Ich habe Franks Code etwas angepaßt, allerdings benutze ich die Zelle G1, die als Datum formatiert sein sollte und die Formel =Heute() enthält. Das hat den Vorteil, daß man "schnell" mal testhalber ein anderes Datum reinschreiben kann.
Der Code dazu:

Public Sub Filter_festschreiben()
Dim gefiltert As Range, z&, bis&
Range("A3:I3").AutoFilter Field:=1, Criteria1:=">=" & CDbl(Range("G1")), _
      Operator:=xlAnd, Criteria2:="<=" & CDbl(Range("G1"))
Set gefiltert = ActiveSheet.UsedRange.Offset(3, 0).SpecialCells(xlCellTypeVisible)
Range("A3:I3").AutoFilter
z = gefiltert(1, 1).Row
bis = gefiltert.Rows.Count + z - 1
Sheets(1).Range(Cells(z, 1), Cells(bis, 9)).Copy
Sheets(1).Cells(z, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Kann mir mal einer erklären, wie pastespecial funktioniert? Ich hab's einfach nicht hinbekommen, gefiltert.copy zu pasten - deshalb der Umweg über die Zeilennummern. Stillschweigende Voraussetzung ist, daß die Datümer sortiert sind.
Schöne Grüße,
Michael

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: Reinhard
Geschrieben am: 29.06.2015 11:19:34
Hallo Michael,
vielen Dank für Dein Makro, es funktioniert einwandfrei.
Wenn ich weitere Spalten in die Datei einfügen tue, was muss ich anspassen im Makro.
Die Range anzupassen ist logisch, aber im unterem Bereich kann ich es mir nicht erklären.
z = gefiltert(1, 1).Row
bis = gefiltert.Rows.Count + z - 1
Sheets(1).Range(Cells(z, 1), Cells(bis, 9)).Copy
VG Reinhard

Bild

Betrifft: AW: Zeilen mit Formeln in Wert ändern
von: AlexG
Geschrieben am: 29.06.2015 18:46:56
Hallo Rainhard,
In der Zeile


Sheets(1).Range(Cells(z, 1), Cells(bis, 9)).Copy
steht die 9 im Bereich bei Cells(bis, 9) für die Spaltennummer.
Gruß
Alex

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zeilen mit Formeln in Wert ändern"