☰

Zellbezüge durch Werte ersetzen ohne F9 per Makro

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
Zellbezüge durch Werte ersetzen ohne F9 per Makro

Dies ist eine Excel-Makrofrage. Gegeben sei eine Reihe von Formeln wie die folgenden: 

= A1 + A2
= B2 + B2

usw., ...

Ich möchte jeden dieser Zellverweise durch die Werte ersetzen, ohne die ganze Zelle zu ersetzen.

= 1 + 2
= 3 + 4

Die Möglichkeit, dies manuell zu tun, besteht darin, jeden Verweis in der Formel hervorzuheben und zu drücken, F9um die Verweise durch Werte zu ersetzen. Ich muss dies jedoch für Tausende von Zellen tun. Gibt es ein Makro, um dies automatisch zu erledigen?

Antwort1

Das folgende Modul verwendet einen Bereich und ersetzt Zellreferenzen durch Werte, die in den referenzierten Zellen gefunden wurden. Wird verwendet, Range.Precedentsum die referenzierten Zelladressen in eine Zeichenfolge umzuwandeln und Splitdiese in ein Array von Zelladressen umzuwandeln. Dieses Array wird dann verwendet, um übereinstimmenden Text in der Formel der Zielzelle zu finden und zu ersetzen.

Verwendung

  • Kopieren Sie den gesamten Code in ein Standardcodemodul im Visual Basic-Editor.
  • Rufen Sie das Unterprogramm mit auf Call ReplaceReferencesWithValues(yourRange).

Das TestMakro ruft die Subroutine auf, um die Referenzen im ausgewählten Bereich zu ersetzen.

Code

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

Anliegen

  • Die Ersetzung erfolgt als Stringund als solche, wenn die Formel sowohl einen Verweis auf dieZelle"A1" und anderer Text, der "A1" enthält, wird der entsprechende Text ebenfalls durch den Wert von ersetzt.Zelle"A1".
  • Formeln, die zum Funktionieren einen Bereich benötigen (z. B. ) SUM, funktionieren nicht mehr, wenn Sie versuchen, die Referenzen durch Zellwerte zu ersetzen.

Antwort2

Obwohl die gegebene @natancodes-Lösung machbar erscheint, ist die ursprüngliche Frage keine einfache Aufgabe, da sie Text durch Text ersetzt. Sie wird in diesem einfachen Fall abgebrochen: Formel sucht nach A1, wenn A1, A11, AA11 oder AA1 erscheinen, Referenzen werden versucht, mit falschen Werten zu überschreiben, und Makros werden nicht einmal funktionieren (abgesehen von „On Error Resume Next“). Verwenden Sie besser einen RegEx für die Such- und Ersetzungsoperation. Ein RegEx-Ausdruck wird hier bereitgestellt, aber aufgrund mangelnder Vertrauenswürdigkeit bei Ausdrücken bin ich mir nicht ganz sicher, ob er trotzdem funktioniert, und muss weiter getestet werden: ('?[a-zA-Z0-9\s[].]{1,99})?'?!?$?[a-zA-Z]{1,3}$?[0-9]{1,7}(:$?[a-zA-Z]{1,3}$?[0-9]{1,7})? (es kam vonhttps://www.get-digital-help.com/zellreferenzen-aus-einer-formel-extrahieren/).

Anderer Pfad, hier detaillierthttps://fastexcel.wordpress.com/2013/10/27/parsing-functions-from-excel-formulas-using-vba-is-mid-or-a-byte-array-the-best-method/, scheint etwas umständlich, aber es könnte in diesen Sonderfällen funktionieren.

verwandte Informationen