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.

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

: Last Updated: 04 May 2020