Zellen in Excel mit #BEZUG! bzw. #REF! Fehler finden

<p>In der Regel ist es offensichtlich wenn eine Formel einen Fehler enthält da dieser auf der Wert-Seite mit <span style="color: #ff0000;">#BEZUG!</span> dargestellt wird. Sollte es aber eine Arbeitsmappe mit vielen Tabelle und darin etlichen Formeln / Verweisen sein kann schon mal eine übersehen werden.<br /><br />Der erste Schritt ist immer per <strong>STRG-R</strong> nach "<strong><span style="color: #ff0000;">#BEZUG!</span></strong>" suchen. Danach muss die Formel neu erstellt / verbessert werden. Wenn aber in der Datenüberprüfung ein Bezugs-Fehler ist wird über die einfache Suche der Fehler nicht gefunden. Hier kommt das VBA-Script zum Einsatz.</p>
<p>Einfach den VBA-Editor öffen ( ALT-F11) und folgenden Code dort in ein neues Modul einfügen (ALT-E ALT-M).<br />Vorab den Direktbereich anzeigen lassen (STRG-D). Da dort das Ergebnis ausgegeben wird.<br />Cursor in die Sub setzen und per F5 die Sub starten. Wird jetzt fehlerhafter Bezug gefunden wird die betroffenen Tabelle samt Felder ausgedruckt.</p>
<p>&nbsp;</p>
<!-- HTML generated using hilite.me -->
<div style="background: #ffffff; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;"><span style="color: #000080; font-weight: bold;">Sub</span> Find_REF_Error()
<span style="color: #000080; font-weight: bold;">Dim</span> oWB <span style="font-weight: bold;">As</span> Workbook, oWSs <span style="font-weight: bold;">As</span> Worksheets, oWS <span style="font-weight: bold;">As</span> Worksheet, oCell <span style="font-weight: bold;">As</span> <span style="color: #000080; font-weight: bold;">Object</span>, oO <span style="font-weight: bold;">As</span> <span style="color: #000080; font-weight: bold;">Object</span>
<span style="color: #000080; font-weight: bold;">Dim</span> i%, j%, r%, c%, rMax%, cMax%, sValidationExternalLink$, sExternalLink$, sFormatConditions$, sNames$, s$

<span style="color: #008800; font-style: italic;">' #BEZUG! Fehler finden (#REF!)</span>

<span style="color: #008800; font-style: italic;">' Felder finden mit Fehler</span>
<span style="color: #000080; font-weight: bold;">Const</span> cfExternalLink = <span style="color: #000080; font-weight: bold;">True</span>

<span style="color: #008800; font-style: italic;">' Externe Links mit Fehler</span>
<span style="color: #000080; font-weight: bold;">Const</span> cfValidationExternalLink = <span style="color: #000080; font-weight: bold;">True</span>
<span style="color: #008800; font-style: italic;">' Wenn etwas gefunden wird den Befehl "Ungültige Daten einkreisen" ausführen.</span>
<span style="color: #008800; font-style: italic;">' Es werden aber nicht immer alle fehlerhaften Felder markiert. Die Formel =#BEZUG! wird nicht markiert.</span>
<span style="color: #008800; font-style: italic;">' Markierung wieder löschen: Register Daten &gt; Bereich Datentools &gt; Datenüberprüfung &gt; Gültigkeitskreise löschen</span>
<span style="color: #000080; font-weight: bold;">Const</span> cfHiglightFields = <span style="color: #000080; font-weight: bold;">True</span>


<span style="color: #008800; font-style: italic;">' Bedinge Formatierungen mit Fehler</span>
<span style="color: #000080; font-weight: bold;">Const</span> cfFormatConditions = <span style="color: #000080; font-weight: bold;">True</span>

<span style="color: #008800; font-style: italic;">' Namen (Feldbezeichnung) im Names-Manager</span>
<span style="color: #000080; font-weight: bold;">Const</span> cfNames = <span style="color: #000080; font-weight: bold;">True</span>


Application.ScreenUpdating = <span style="color: #000080; font-weight: bold;">False</span>
Application.Visible = <span style="color: #000080; font-weight: bold;">False</span>


<span style="color: #000080; font-weight: bold;">On</span> <span style="color: #000080; font-weight: bold;">Error</span> <span style="color: #000080; font-weight: bold;">Resume</span> <span style="color: #000080; font-weight: bold;">Next</span>
<span style="color: #000080; font-weight: bold;">Set</span> oWB = ActiveWorkbook
<span style="color: #000080; font-weight: bold;">For</span> <span style="color: #000080; font-weight: bold;">Each</span> oWS <span style="font-weight: bold;">In</span> oWB.Worksheets
<span style="color: #000080; font-weight: bold;">With</span> oWS
.Select
<span style="color: #008800; font-style: italic;">' Die Zelle markieren welche den äußersten Rand des Zellbereichs markiert mit veränderten Zellen</span>
ActiveCell.SpecialCells(xlLastCell).Select

rMax = ActiveCell.Row
cMax = ActiveCell.Column

<span style="color: #000080; font-weight: bold;">For</span> r = <span style="color: #0000ff;">1</span> <span style="color: #000080; font-weight: bold;">To</span> rMax
<span style="color: #000080; font-weight: bold;">For</span> c = <span style="color: #0000ff;">1</span> <span style="color: #000080; font-weight: bold;">To</span> cMax
<span style="color: #000080; font-weight: bold;">Set</span> oCell = oWS.Cells(r, c)

<span style="color: #000080; font-weight: bold;">With</span> oCell
<span style="color: #000080; font-weight: bold;">If</span> cfExternalLink <span style="color: #000080; font-weight: bold;">Then</span>
s = .Formula
<span style="color: #000080; font-weight: bold;">If</span> s &lt;&gt; <span style="color: #0000ff;">""</span> <span style="color: #000080; font-weight: bold;">Then</span>
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sExternalLink = sExternalLink &amp; Replace(oCell.Address(<span style="color: #000080; font-weight: bold;">True</span>, <span style="color: #000080; font-weight: bold;">False</span>), <span style="color: #0000ff;">"$"</span>, vbNullString, , <span style="color: #0000ff;">1</span>, vbTextCompare) &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">If</span> cfValidationExternalLink <span style="color: #000080; font-weight: bold;">Then</span>
s = vbNullString
s = .Validation.Formula1
<span style="color: #000080; font-weight: bold;">If</span> s &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sValidationExternalLink = sValidationExternalLink &amp; Replace(oCell.Address(<span style="color: #000080; font-weight: bold;">True</span>, <span style="color: #000080; font-weight: bold;">False</span>), <span style="color: #0000ff;">"$"</span>, vbNullString, , <span style="color: #0000ff;">1</span>, vbTextCompare) &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
s = vbNullString
s = .Validation.Formula2
<span style="color: #000080; font-weight: bold;">If</span> s &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sValidationExternalLink = sValidationExternalLink &amp; Replace(oCell.Address(<span style="color: #000080; font-weight: bold;">True</span>, <span style="color: #000080; font-weight: bold;">False</span>), <span style="color: #0000ff;">"$"</span>, vbNullString, , <span style="color: #0000ff;">1</span>, vbTextCompare) &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">If</span> cfFormatConditions <span style="color: #000080; font-weight: bold;">Then</span>
j = .FormatConditions.Count
<span style="color: #000080; font-weight: bold;">For</span> i = <span style="color: #0000ff;">1</span> <span style="color: #000080; font-weight: bold;">To</span> j
<span style="color: #000080; font-weight: bold;">Set</span> oO = .FormatConditions.Item(i)
s = vbNullString
s = oO.Formula1
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sFormatConditions = sFormatConditions &amp; Replace(.Address(<span style="color: #000080; font-weight: bold;">True</span>, <span style="color: #000080; font-weight: bold;">False</span>), <span style="color: #0000ff;">"$"</span>, vbNullString, , <span style="color: #0000ff;">1</span>, vbTextCompare) &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">Exit</span> <span style="color: #000080; font-weight: bold;">For</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
s = vbNullString
s = oO.Formula2
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sFormatConditions = sFormatConditions &amp; Replace(.Address(<span style="color: #000080; font-weight: bold;">True</span>, <span style="color: #000080; font-weight: bold;">False</span>), <span style="color: #0000ff;">"$"</span>, vbNullString, , <span style="color: #0000ff;">1</span>, vbTextCompare) &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">Exit</span> <span style="color: #000080; font-weight: bold;">For</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">Next</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">With</span>
<span style="color: #000080; font-weight: bold;">Next</span>

<span style="color: #000080; font-weight: bold;">Next</span>

<span style="color: #000080; font-weight: bold;">If</span> sExternalLink &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
Debug.Print vbCrLf &amp; <span style="color: #0000ff;">"#BEZUG!/#REF! Fehler in folgenden Zellen der Tabelle '"</span> &amp; oWS.Name &amp; <span style="color: #0000ff;">"' gefunden."</span>
Debug.Print Left(sExternalLink, Len(sExternalLink) - <span style="color: #0000ff;">1</span>)
sExternalLink = vbNullString
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">If</span> sValidationExternalLink &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
Debug.Print vbCrLf &amp; <span style="color: #0000ff;">"#BEZUG!/#REF! Fehler in der Datenüberprüfung folgender Zellen der Tabelle '"</span> &amp; oWS.Name &amp; <span style="color: #0000ff;">"' gefunden. "</span> &amp; IIf(cfHiglightFields, <span style="color: #0000ff;">"Zellen wurden rot umkreist!"</span>, vbNullString)
Debug.Print Left(sValidationExternalLink, Len(sValidationExternalLink) - <span style="color: #0000ff;">1</span>)
sValidationExternalLink = vbNullString
<span style="color: #000080; font-weight: bold;">If</span> cfHiglightFields <span style="color: #000080; font-weight: bold;">Then</span> ActiveSheet.CircleInvalid
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">If</span> sFormatConditions &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
Debug.Print vbCrLf &amp; <span style="color: #0000ff;">"#BEZUG!/#REF! Fehler in bedinger Formatierung folgender Zellen der Tabelle '"</span> &amp; oWS.Name &amp; <span style="color: #0000ff;">"' gefunden. "</span> &amp; IIf(cfHiglightFields, <span style="color: #0000ff;">"Zellen wurden rot umkreist!"</span>, vbNullString)
Debug.Print Left(sFormatConditions, Len(sFormatConditions) - <span style="color: #0000ff;">1</span>)
sFormatConditions = vbNullString
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>


<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">With</span>
<span style="color: #000080; font-weight: bold;">Next</span>

<span style="color: #000080; font-weight: bold;">If</span> cfNames <span style="color: #000080; font-weight: bold;">Then</span>
<span style="color: #000080; font-weight: bold;">With</span> oWB
j = .Names.Count
<span style="color: #000080; font-weight: bold;">For</span> i = <span style="color: #0000ff;">1</span> <span style="color: #000080; font-weight: bold;">To</span> j
<span style="color: #000080; font-weight: bold;">Set</span> oO = .Names.Item(i)
s = vbNullString
s = oO.Value
<span style="color: #000080; font-weight: bold;">If</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#BEZUG!"</span>, vbTextCompare) <span style="font-weight: bold;">Or</span> InStr(<span style="color: #0000ff;">1</span>, s, <span style="color: #0000ff;">"#REF!"</span>, vbTextCompare) <span style="color: #000080; font-weight: bold;">Then</span>
sNames = sNames &amp; oO.Name &amp; <span style="color: #0000ff;">"|"</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">Next</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">With</span>

<span style="color: #000080; font-weight: bold;">If</span> sNames &lt;&gt; vbNullString <span style="color: #000080; font-weight: bold;">Then</span>
Debug.Print vbCrLf &amp; <span style="color: #0000ff;">"#BEZUG!/#REF! Fehler in benannte Zellen / -Zellbreiche gefunden (Names-Manager)"</span>
Debug.Print Left(sNames, Len(sNames) - <span style="color: #0000ff;">1</span>)
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">If</span>

<span style="color: #000080; font-weight: bold;">Set</span> oCell = <span style="color: #000080; font-weight: bold;">Nothing</span>
<span style="color: #000080; font-weight: bold;">Set</span> oWB = <span style="color: #000080; font-weight: bold;">Nothing</span>
Application.Visible = <span style="color: #000080; font-weight: bold;">True</span>
Application.ScreenUpdating = <span style="color: #000080; font-weight: bold;">True</span>
<span style="color: #000080; font-weight: bold;">End</span> <span style="color: #000080; font-weight: bold;">Sub</span>
</pre>
</div>
<p>&nbsp;</p>