Brian muss ein Arbeitsblatt verarbeiten, bevor es an andere Personen weitergegeben werden kann. Was er braucht, ist, die meisten, aber nicht alle Formeln im Arbeitsblatt zu entfernen. Er möchte alle Zellen in einem ausgewählten Zellbereich durchlaufen und, wenn die Zelle eine Formel enthält, diese Formel überprüfen. Wenn die Formel einen Verweis (einen beliebigen Verweis) auf ein anderes Arbeitsblatt in der aktuellen Arbeitsmappe enthält, wird die Formel ignoriert. Wenn die Formel keine solche Referenz enthält, muss das Makro die Formel durch das Ergebnis der Formel ersetzen.

Dies ist eine relativ einfache Aufgabe. Alles, was Sie tun müssen, ist, Ihren Makroschritt durch die Zellen zu führen und herauszufinden, ob die Zelle eine Formel enthält. Wenn dies der Fall ist, überprüfen Sie, ob die Formel ein Ausrufezeichen enthält. Ausrufezeichen werden in Formelreferenzen wie den folgenden verwendet:

=Sheet2!A1

Wenn die Formel ein Ausrufezeichen enthält, können Sie es ignorieren. Wenn es kein Ausrufezeichen enthält, können Sie es durch seinen Wert ersetzen.

Sub ConvertFormulas1()

Dim c As Variant     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             If InStr(1, frm, "!") = 0 Then                 c.Value = c.Value             End If         End If     Next c End Sub

Dieser Ansatz hat einen Nachteil: Das Ausrufezeichen wird in allen Formeln außerhalb des aktuellen Arbeitsblatts angezeigt, einschließlich der Formeln in anderen Arbeitsmappen. Wenn Sie wirklich nur Formeln durch andere Arbeitsblätter in der aktuellen Arbeitsmappe ersetzen möchten, aber Formeln ignorieren möchten, die auf Blätter in anderen Arbeitsmappen verweisen, müssen Sie zusätzliche Logik hinzufügen. Die Logik wird deutlich, wenn Sie sich ansehen, wie Excel auf diese anderen Arbeitsmappen verweist:

=[OtherWorksheet.xls]Sheet1'!$C$9

Beachten Sie, dass der Name der anderen Arbeitsmappe in Klammern steht.

Nach dem Testen des Ausrufezeichens (das Sie darüber informiert, dass sich der Verweis auf ein anderes Arbeitsblatt befindet, müssen Sie daher prüfen, ob eine linke Klammer vorhanden ist. Wenn diese vorhanden ist, bezieht sich der Verweis nicht auf eine Zelle in der aktuellen Arbeitsmappe.

Sub ConvertFormulas2()

Dim c As Variant     Dim OtherSheet As Boolean     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             OtherSheet = False             If InStr(1, frm, "!") Then                 OtherSheet = True                 If InStr(1, frm, "[") Then                     OtherSheet = False                 End If             End If             If Not OtherSheet Then                 c.Value = c.Value             End If         End If     Next c End Sub

Es sollte darauf hingewiesen werden, dass es relativ einfach wäre, die in diesem Makro verwendete Formel so zu ändern, dass alle externen Referenzen beim Verlassen entfernt werden Die Verweise auf das aktuelle Arbeitsblatt sind intakt. Alles, was Sie tun müssen, ist, die Prüfung für die Klammer zu entfernen und dann das Schlüsselwort „Not“ in der Struktur zu entfernen, die die Variable OtherSheet überprüft.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite mit hilfreichen Informationen vorbereitet .

Klicken Sie hier, um diese spezielle Seite in a zu öffnen neuer Browser-Tab .

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (12158) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Ersetzen einiger Formeln durch die Formelergebnisse.