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

In der Regel ist es offensichtlich wenn eine Formel einen Fehler enthält da dieser auf der Wert-Seite mit #BEZUG! dargestellt wird. Sollte es aber eine Arbeitsmappe mit vielen Tabelle und darin etlichen Formeln / Verweisen sein kann schon mal eine übersehen werden. Der erste Schritt ist immer per STRG-R nach "#BEZUG!" 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.
Einfach den VBA-Editor öffen ( ALT-F11) und folgenden Code dort in ein neues Modul einfügen (ALT-E ALT-M). Vorab den Direktbereich anzeigen lassen (STRG-D). Da dort das Ergebnis ausgegeben wird. Cursor in die Sub setzen und per F5 die Sub starten. Wird jetzt fehlerhafter Bezug gefunden wird die betroffenen Tabelle samt Felder ausgedruckt.
 

<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%;">Sub Find_REF_Error()
Dim oWB As Workbook, oWSs As Worksheets, oWS As Worksheet, oCell As Object, oO As Object
Dim i%, j%, r%, c%, rMax%, cMax%, sValidationExternalLink$, sExternalLink$, sFormatConditions$, sNames$, s$

' #BEZUG! Fehler finden (#REF!)

' Felder finden mit Fehler
Const cfExternalLink = True

' Externe Links mit Fehler
Const cfValidationExternalLink = True
' Wenn etwas gefunden wird den Befehl "Ungültige Daten einkreisen" ausführen.
' Es werden aber nicht immer alle fehlerhaften Felder markiert. Die Formel =#BEZUG! wird nicht markiert.
' Markierung wieder löschen: Register Daten > Bereich Datentools > Datenüberprüfung > Gültigkeitskreise löschen
Const cfHiglightFields = True

' Bedinge Formatierungen mit Fehler
Const cfFormatConditions = True

' Namen (Feldbezeichnung) im Names-Manager
Const cfNames = True

Application.ScreenUpdating = False
Application.Visible = False

On Error Resume Next
Set oWB = ActiveWorkbook
For Each oWS In oWB.Worksheets
With oWS
.Select
' Die Zelle markieren welche den äußersten Rand des Zellbereichs markiert mit veränderten Zellen
ActiveCell.SpecialCells(xlLastCell).Select

rMax = ActiveCell.Row
cMax = ActiveCell.Column

For r = 1 To rMax
For c = 1 To cMax
Set oCell = oWS.Cells(r, c)

With oCell
If cfExternalLink Then
s = .Formula
If s <> "" Then
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sExternalLink = sExternalLink & Replace(oCell.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
End If
End If
End If

If cfValidationExternalLink Then
s = vbNullString
s = .Validation.Formula1
If s <> vbNullString Then
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sValidationExternalLink = sValidationExternalLink & Replace(oCell.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
End If
End If
s = vbNullString
s = .Validation.Formula2
If s <> vbNullString Then
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sValidationExternalLink = sValidationExternalLink & Replace(oCell.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
End If
End If
End If

If cfFormatConditions Then
j = .FormatConditions.Count
For i = 1 To j
Set oO = .FormatConditions.Item(i)
s = vbNullString
s = oO.Formula1
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sFormatConditions = sFormatConditions & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
Exit For
End If
s = vbNullString
s = oO.Formula2
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sFormatConditions = sFormatConditions & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
Exit For
End If
Next
End If

End With
Next

Next

If sExternalLink <> vbNullString Then
Debug.Print vbCrLf & "#BEZUG!/#REF! Fehler in folgenden Zellen der Tabelle '" & oWS.Name & "' gefunden."
Debug.Print Left(sExternalLink, Len(sExternalLink) - 1)
sExternalLink = vbNullString
End If

If sValidationExternalLink <> vbNullString Then
Debug.Print vbCrLf & "#BEZUG!/#REF! Fehler in der Datenüberprüfung folgender Zellen der Tabelle '" & oWS.Name & "' gefunden. " & IIf(cfHiglightFields, "Zellen wurden rot umkreist!", vbNullString)
Debug.Print Left(sValidationExternalLink, Len(sValidationExternalLink) - 1)
sValidationExternalLink = vbNullString
If cfHiglightFields Then ActiveSheet.CircleInvalid
End If

If sFormatConditions <> vbNullString Then
Debug.Print vbCrLf & "#BEZUG!/#REF! Fehler in bedinger Formatierung folgender Zellen der Tabelle '" & oWS.Name & "' gefunden. " & IIf(cfHiglightFields, "Zellen wurden rot umkreist!", vbNullString)
Debug.Print Left(sFormatConditions, Len(sFormatConditions) - 1)
sFormatConditions = vbNullString
End If

End With
Next

If cfNames Then
With oWB
j = .Names.Count
For i = 1 To j
Set oO = .Names.Item(i)
s = vbNullString
s = oO.Value
If InStr(1, s, "#BEZUG!", vbTextCompare) Or InStr(1, s, "#REF!", vbTextCompare) Then
sNames = sNames & oO.Name & "|"
End If
Next
End With

If sNames <> vbNullString Then
Debug.Print vbCrLf & "#BEZUG!/#REF! Fehler in benannte Zellen / -Zellbreiche gefunden (Names-Manager)"
Debug.Print Left(sNames, Len(sNames) - 1)
End If
End If

Set oCell = Nothing
Set oWB = Nothing
Application.Visible = True
Application.ScreenUpdating = True
End Sub
</pre>
</div>

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

Hauptmenü

Anmeldeformular