Zellen mit Formeln fixieren

[Zwergel]

Hi,
wie kann ich Zellen in einer Spalte der Tabelle mit Formeln als Wert fixieren, wenn die Formel einen Wert ergibt? Mit VBA.

[Kl@us-M.]

Hi Micha,

es mag an der vorgerückten Stunde liegen, aber diese Deine Frage verstehe ich nun garnicht.

Was soll "fixiert" werden? Wann ergeben die Formeln einen Wert und wann nicht?

Kannst Du mal näher erklären, was wann wo und wie per VBA passieren soll?

[Zwergel]

Hi K@M,

liegt sicher nicht an der Zeit, aber ich wußte auch nicht wie ich das formulieren soll:

Inhaltsverzeichnis

	C
5	Bohrmaschine
6	Waschmaschine
7	Vortlaufend
8

Formeln der Tabelle

Zelle Formel C5 =WENNFEHLER(INDEX(Geräte!B$3:Geräte!Z$3;1+11*(ZEILE(Geräte!A1)-1));"") C6 =WENNFEHLER(INDEX(Geräte!B$3:Geräte!Z$3;1+11*(ZEILE(Geräte!A2)-1));"") C7 =WENNFEHLER(INDEX(Geräte!B$3:Geräte!Z$3;1+11*(ZEILE(Geräte!A3)-1));"") C8 =WENNFEHLER(INDEX(Geräte!B$3:Geräte!Z$3;1+11*(ZEILE(Geräte!A4)-1));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

In dem Beispiel wirds vielleicht deutlich: bis C7 stehen Werte in der Zelle, ab C8 eben "", da die Bedingungen nicht erfüllt werden. Ich möchte mittels VBA nun erreichen, daß die Zellen bis C7 oder später ermittelt werden, wo das Ergebnis der Formel eben nicht "" ergibt und diese Zellen dann fixieren, also statt der Formel den Wert (Formelergebnis) in die Zelle eintragen.
 Die Zellen danach in der Spalte sollen weiter die Formel enthalten.

Die Aktion soll eine Sortierung der Spalte ermöglichen, die mit Formeln nicht möglich ist.

Hoffe nun ist der Plan verständlich.

[Käpt'n Blaubär]

Hallo Michael,

so ganz wirklich habe ich es immer noch nicht verstanden.
Willst Du sowas erreichen? ... dann möge es nützlich sein.

'   Soll ein markierter Zellbereich keine Formeln mehr enthalten, können diese
'   Formeln in Festwerte umgewandelt werden:

Sub Formeln_In_Festwerte_I ()
    Dim Zelle As Range
    Dim Meldung As String
    Meldung = MsgBox("Sollen Formeln im markierten Zellbereich" & vbNewLine & _
        "wirklich in Festwerte umgewandelt werden ?" & vbNewLine & vbNewLine & _
        "Diese Änderung ist nicht rückgängig zu machen !", vbYesNo, "ACHTUNG!")
    If Meldung = "7" Then Exit Sub
    On Error Resume Next
    For Each Zelle In Selecion
        If Zelle.HasFormula = True Then Zelle.Formula = Zelle.Value
    Next Zelle
End Sub



'   Soll ein markierter Zellbereich keine Formeln mehr enthalten, kann dies
'   auch per Copy&Paste realisiert werden:

Sub Formeln_In_Festwerte_ II ()
    Dim WS As Worksheet
    Set WS = This Workbook.Worksheets("Tabelle1")
    With WS.Range("A1:Z1000")
        .Copy
        .PasteSpecial xlPaste.Values
    End With
    Application.CutCopyMode = False
End Sub


* wenn ich mich recht erinnere, stammen die Codes von Russi

[Zwergel]

Moin Peter,

vielen Dank für deinen Lösungsansatz, wenn dort nun der Bezug nicht auf einen fixen Bereich vorgegeben wäre, sondern der Bereich ermittelt würde, der als Ergebnis der Formel eben z.B. die ersten 3 Ergebniszeilen ermittelt hätte und das dann als umzuwandelknden Bereich berücksichtigt, dann wäre die Lösung perfekt.

Also mal als Ablaufbeschreibung:
Ermittle per Code in Bereich C5: xxxxx, ob die Formel dieser Zelle einen Rückgabewert ergibt, ungleich Leer
wenn ja, wandel den Bereich oder diese Zelle um von Formel in Wert
sonst Ende des Codes.

Oder anders herum,
ermitle die erste Zelle in Bereich C5: xxxx, die als Formelergebnis Leer ergibt und wandle die Zellen darüber bis C5 um in fixen Wert.

[BoskoBiati]

Hi Michael,

als ganz einfache Variante:

Code:

Sub Test()
Dim loCo As Long
Dim loLetzte As Long
Dim rng As Range
loLetzte = Cells(Rows.Count, 3).End(xlUp).Row

    For loCo = 5 To loLetzte
        Set rng = Cells(loCo, 3)
        If rng <> "" Then rng.Value = rng.Value
    Next

End Sub

[Kuwer]

Hallo Micha,

Sub FormelnzuWerten()
  Dim rngF As Range
  For Each rngF In Range(Range("C5"), Range("C5").End(xlDown).SpecialCells(xlCellTypeFormulas))
    If Len(rngF.Value) Then
      rngF.Value = rngF.Value
    Else
      Exit For
    End If
  Next rngF
End Sub

Gruß Uwe

[Zwergel]

Hi,

mit Edgars Code klappts soweit, aber da die Formel in C10:C11 statt des Leer eine Null einträgt, knallt der Code dort die Formel auch raus.

Das müßte noch geändert werden, wobei mir nicht klar ist, warum da die Null auftritt.

Bei Uwes Code behandelt der Code statt C die Spalte B, das verstehe ich absolut nicht.

[Kuwer]

Hi Micha,

Bei Uwes Code behandelt der Code statt C die Spalte B, das verstehe ich absolut nicht.

ich auch nicht.  

Aber es war trotzdem nicht ganz richtig. Kannst ja nochmal damit probieren:

Sub FormelnzuWerten()
 Dim rngF As Range
 On Error Resume Next
 For Each rngF In Range(Range("C5"), Range("C5").End(xlDown)).SpecialCells(xlCellTypeFormulas)
   If Len(rngF.Value) Then
     rngF.Value = rngF.Value
   Else
     Exit For
   End If
 Next rngF
 On Error GoTo 0
End Sub

Gruß Uwe

[BoskoBiati]

Hi Michael,


wenn die Formel stimmt, die Du gepostet hast, dann kann die keine 0 auswerfen, da sie ab C8 abwärts in einen Fehler läuft. Dein Bezugsbereich ist B3:Z3, ab Zeile 8 bist Du aber außerhalb des Bereichs (AI3).