Hi,
wie kann ich Zellen in einer Spalte der Tabelle mit Formeln als Wert fixieren, wenn die Formel einen Wert ergibt? Mit VBA.
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?
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.
Hallo Michael,
so ganz wirklich habe ich es immer noch nicht verstanden.
Willst Du sowas erreichen? ... dann möge es nützlich sein.
Zitat:' 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
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.
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
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
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.
Hi Micha,
(16.10.2017, 10:34)Zwergel schrieb: [ -> ]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
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).