Wie kann man mit Excel 2021 Formeln in Werte per Makro umwandel?

Question

Hallo Zusammen,

bisher habe ich folgendes Makro verwendet:

Vor der Ausführung den Bereich makieren!

Sub Formeln_durch_Werte_ersetzen()

' Makro ersetzt Formeln durch ihre Werte

Dim i, j, ZlCells, ZlCols, intWert As Long

' Spaltendurchlauf vorbereiten (=j)
If Selection.Columns.Count = 256 Then
    ZlCols = 0
Else
    ZlCols = ActiveCell.Column
End If

' Zeilendurchlauf vorbereiten (=i)
If Selection.Cells.Count = 65536 Then
    ZlCells = 0
Else
    ZlCells = ActiveCell.Row - 1
End If

' Spaltendurchlauf
For j = ActiveCell.Column To Selection.Columns.Count + ZlCols

    'Zeilendurchlauf
    For i = ActiveCell.Row To Selection.Rows.Count + ZlCells

        ' Prüfe: Zelle/Zellbereich markiert, nicht leer und mind. 1 tats. Formel
        If IsNull(Cells(i, j).Value) = False And Not _
            Cells(i, j).Value = "" And _
            Left(Cells(i, j).Formula, 1) = "=" Then
                intWert = intWert + 1
        End If

    Next i

Next j

' Hier muss > durch das Größer-Zeichen ersetzt werden
' Es lässt sich hier leider nicht darstellen :(
If intWert > 0 Then
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Application.CutCopyMode = False
    MsgBox "Formeln in " & intWert & " Zellen durch ihren Wert ersetzt!", , "Information"
Else
    MsgBox "Markierte Zelle(n) leer oder ohne Formeln! " & _
        "Es wurde keine Ersetzung vorgenommen!", , "Fehlerhafer Inhalt"
End If
End Sub

Seid wir auf Office 2021 umgestiegen sind, findet das Makro keine Formeln mehr. Hat dafür jemand eine Lösung?

Gruss

Ines

Answer 1

Hi Ines,

Formeln in ihre Werte umwandeln geht doch ganz einfach: Bereich markieren -> Kopieren (Strg+C) -> Einfügen -> Werte einfügen. Dazu muss doch vorher nicht geprüft werden, ob jede Zelle eine Formel enthält, denn falls eine Zelle davon leer oder bereits mit einem fixen Wert gefüllt ist, tut das dem Einfügen der Werte keinen Abbruch und löst deshalb auch keinen Fehler aus.

Wie viele Formeln in einem selektierten Bereich vorhanden sind, kannst du außerdem ganz einfach ermitteln:

    Dim varFormel As Variant
    On Error Resume Next
    varFormel = Selection.SpecialCells(xlFormulas).Count
    On Error Resume Next
    If varFormel <> "" Then MsgBox "Der selektierte Bereich enthält " & varFormel & " Formeln"

Bis später, Karin