Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1660to1664
1660to1664
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellen mit #REF Error finden und löschen

Forumthread: Zellen mit #REF Error finden und löschen

Zellen mit #REF Error finden und löschen
13.12.2018 12:46:37
PurePowerPhil
Hi liebe Community,
Kann mir vllt jemand helfen? Ich habe schon diverse Google Suchen hinter mir aber hat mir bisher nicht recht weitergeholfen.
Also ich habe ein Overview-Sheet das Daten von diversen anderen Sheets zusammenfasst. Diese sind einfach per Referenz eingefügt.
Sobald nun in einem der diversen anderen Sheets eine neue Zeile mit einen neuen Eintrag einfüge und diese später wieder lösche, erscheint ein #REF Error im Overview-Sheet.
Nun möchte ich verhindern das dieser Ref Error im Overview auftaucht bzw. die Zeile mit dem Fehler löschen.
Dies habe ich mit einen einfachen Button realisiert.
Per Klick auf den Button wird das Overview-Sheet nach Ref Error's abgesucht.. Nur funktioniert das ganze nicht..
Der Debugger wirft einen Run-time Fehler '13' Type mismatch.
Hier ist mein Code: 

Private Sub UpdateButton_Click()
Dim iRow As Long
Dim i As Long
Dim check_txt As String
iRow = ThisWorkbook.Sheets("MGMT-Overview").Cells(Rows.Count, 12).End(xlUp).Row
'Color in the important Content
ThisWorkbook.Sheets("MGMT-Overview").Range("F4:H" & iRow).Interior.Color = RGB(214, 220, 228)
'Search for any false Values in Sheet
For i = 4 To iRow
'Beide Versionen durch Google suchen gefunden und ohne erfolg ausprobiert
If Cells(2, i).Value = CVErr(xlErrRef) Then
'If (Application.IsError(Cells(2, i).Value)) Then
ThisWorkbook.Sheets("MGMT-Overview").Rows(iRow).EntireRow.Delete
i = i - 1
End If
Next i
End Sub

Anzeige

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen mit #REF Error finden und löschen
13.12.2018 13:29:58
Nepumuk
Hallo Phil,
im Prinzip so:
Private Sub UpdateButton_Click()
    
    Dim iRow As Long
    Dim i As Long
    
    With ThisWorkbook.Worksheets("MGMT-Overview")
        
        iRow = .Cells(.Rows.Count, 12).End(xlUp).Row
        
        'Color in the important Content
        .Range("F4:H" & CStr(iRow)).Interior.Color = RGB(214, 220, 228)
        
        'Search for any false Values in Sheet
        For i = iRow To 4 Step -1
            
            If IsError(.Cells(2, i).Value) Then
                
                If .Cells(2, i).Value = CVErr(xlErrRef) Then
                    
                    .Rows(iRow).Delete
                    
                End If
            End If
        Next i
    End With
End Sub

Jetzt kommt ein Großes ABER: Irgendwas an deinem Code stimmt nicht, denn du ermittelst die Anzahl der Zeilen in einer Spalte, aber in der Schleife sprichst du die Zellen in Zeile 2 an.
Gruß
Nepumuk
Anzeige
AW: Zellen mit #REF Error finden und löschen
13.12.2018 13:43:42
PurePowerPhil
Hi Nepumuk,
Danke erstmal für die schnelle Antwort. Deine Schleifen Deklaration ist mir nicht geläufig.
Du lässt die Schleifen Variable von unten nach oben suchen, damit das i = i-1 entfällt oder?
Ja dein Aber ist berechtigt, aber ich habe die Beiden Werte nur vertauscht. Sprich es sollte eig. Cells(i,2) sein und nicht umgekehrt. Danke nochmals ich werde es gleich testen, mit der richtige Variante von Cells. Kannst du mir auch erklären warum mein Code nicht funktioniert hat?
Anzeige
AW: Zellen mit #REF Error finden und löschen
13.12.2018 13:50:45
PurePowerPhil
Also mein Code sieht jetzt so aus: 
Private Sub UpdateButton_Click()
Dim iRow As Long
Dim i As Long
With ThisWorkbook.Worksheets("MGMT-Overview")
iRow = .Cells(.Rows.Count, 12).End(xlUp).Row
'Color in the important Content
.Range("F4:H" & CStr(iRow)).Interior.Color = RGB(214, 220, 228)
'Search for any false Values in Sheet
For i = iRow To 4 Step -1
If IsError(.Cells(i, 2).Value) Then
If .Cells(i, 2).Value = CVErr(xlErrRef) Then
.Rows(i).Delete
End If
End If
Next i
End With
End Sub

Anzeige
AW: Zellen mit #REF Error finden und löschen
13.12.2018 13:56:05
Nepumuk
Prima, genau richtig!!!
AW: Zellen mit #REF Error finden und löschen
13.12.2018 13:54:59
Nepumuk
Hallo Phil,
1. Ja, Schleifen die Zeilen löschen sollten immer von unten nach oben laufen. Ist einfach logischer.
2. Wenn die Zelle keinen Fehlerwert enthält dann läuft dein Vergleich in einen Fehler. Daher auch die vorgeschaltete Abfrage ob die Zelle überhaupt einen Fehler enthält. Ein Fehlerwert in einer Zelle ist ein Objekt, ein "normale" Zellinhalte eine Zahl oder ein Text und der Vergleich eines/einer Textes/Zahl mit einem Objekt klappt nun mal nicht.
Gruß
Nepumuk
Anzeige
;

Forumthreads zu verwandten Themen

#REF Replacement
Hartmut_M

Hallo, kämpfe im Moment mit folgendem Problem.
Mein Tabellenblatt nimmt Bezug auf eine andere Tabelle, die ausgewechselt wird. Heirdurch kommt es zur Meldung: #BEZUG!
Mit folgendem Befehl will ich den Bezug ändern:
Selection.Replace What:="#REF", Replacement:="Controlling " & ja...
07.09.2009 15:24:20
Thread-ID: 1101034
8 Beiträge
Anzeige
#REF Replacement
Hartmut_M

Hallo, hatte gestern schon ein Problem beschrieben (07.09. um 15:13 Uhr). Dank eurer Hilfe funktioneirt das ersetzen jetzt.
Jetzt ist folgendes Phänomen aufgetreten:
Führe ich das Makro im Einzelschritt aus, wird das Ersetzen durchgeführt. Lasse ich es aber automatisch durchlaufen, wird...
08.09.2009 12:17:53
Thread-ID: 1101215
6 Beiträge
Ungültige Namen #REF! und #BEZUG! - Sprachproblem
Stefan

Hallo Leute,
habe ein Problem beim Suchen von gelöschten Namen mittels Names.Item(RefersTo:=Blattname!#BEZUG!").
Das funktioniert, aber nur national. Excel schein hier nicht auf RefersTo, sondern auf RefersToLocal zurückzugreifen. Im englischsprachigen muss sicher #REF! gesucht werden,...
29.12.2012 22:54:30
Thread-ID: 1292374
6 Beiträge
SVERWEIS liefert nur #REF!
Marvin

Hi zusammen,
ich habe folgende Situation.
Ich habe zwei Dateien. Datei 1 eins "Book1.xlsm" und enthaelt im Sheet "Party" eine Tabelle mit zwei Spalten. In Spalte B sind Namen von Herstellern, die in Spalte A in intern oder extern unterteilt werden.

Datei zwei heisst "Zieldatei....
19.01.2015 12:23:44
Thread-ID: 1403632
7 Beiträge
Referenzproblem #Ref! bei Ausschneiden von Zeilen
Zeilen

Ich habe ein Spreadsheet mit 4 Blaettern:
Blatt1 enthaelt Daten von offenen Faellen, Blatt2 enthaelt Daten von geschlossenen Faellen und auf Blatt3 und Blatt4 erfolgen Berechnungen bei denen ich per Formel auf die Daten in Blatt1 und Blatt2 zugreife.
Problem:
Wenn ein Fall abgeschlos...
03.06.2004 13:01:31
Thread-ID: 436851
2 Beiträge
leere Zelle anstatt #Value! und #Ref!
Adrian

Hi all,
weiss jemand von euch, wie man anstelle von #Value! und #Ref! automatisch eine leere Zelle erscheinen lassen kann (ohne viel Programmierarbeit)?
Besten Dank für Eure Hilfe!
Adrian
26.01.2005 08:48:21
Thread-ID: 554800
4 Beiträge
Indexformel =#REF!
Felix

Hallo,
ich habe eine Exceltabelle, die ueber eine Summen- und Indexformel aus einer anderen Datei die kumulierten Monatswerte verwendet. Beim oeffnen der Ausgabedatei kommt richtiger Weise die Frage: Daten aktualisieren? Bei beiden Antworten erscheint die Fehlermeldung =#REF!
1. Was mus...
21.04.2005 08:18:43
Thread-ID: 601723
6 Beiträge
Anzeige
Summe + Index = #Ref!
Felix

Hallo,
ich will aus einer Backrounddatei in einer Uebersichtsdatei kummulierte Monatswerte ausgeben. Da sich die Anzahl der zu kummulierenden Monate / Spalten aus der Backrounddatei jeden Monat aendert, mache ich das mit einer Summen + Indexformel.
Beim oeffenen der Uebersichtsdatei, k...
22.04.2005 08:22:54
Thread-ID: 602341
2 Beiträge
Anzeige
Blattname soll nicht durch #Ref ersetzt werden
gemba

Hallo,
ich habe eine Datei mit Arbeitsblaettern, die jeweils Januar, Februar, Maerz etc. heissen. Zu Beginn jeden Monats wird das aktuelle Arbeitsblatt in die Datei eingefuegt.
In meinem Uebersichtsblatt wuerde ich die Formeln schon fuer alle Monate gerne eingeben, allerdings existieren...
11.05.2006 19:39:11
Thread-ID: 763350
5 Beiträge
860to864: Fehler #REF! beim Löschen der Zeile im Zellbezug
KM

Hallo,
ich habe einen Zellbezug im Tabellenblatt 2 auf das Tabellenblatt 1.
Zelle A1 in Tabelle 2 mit folgender Formel = Zelle A1 von Tabelle 1
Wenn ich die Zeile 1 in Tabelle 1 lösche, kommt grundsätzlich die Fehlermeldung #REF!. Ich möchte aber grundsätzlich, dass in Tabelle 2 Ze...
04.04.2007 21:04:00
Thread-ID: 860475
5 Beiträge
getpivotdata produziert #ref! fehler
Sabine

hallo!
folgende funktion GETPIVOTDATA("Ang.Wert-EUR";hilfstab1!$A$3;"Ang.Nr.";C3) produziert #ref!. Ich denke es liegt am Format von C3, nehme ich statt des Bezuges den Wert funktioniert es. Habe bereits alle Formate durchprobiert komme aber auf keinen grünen Zweig.
Ideen?
Danke im...
30.10.2007 13:57:00
Thread-ID: 921198
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Zellen mit #REF Error in Excel finden und löschen

Schritt-für-Schritt-Anleitung

  1. Öffne dein Excel-Dokument und gehe zum entsprechenden Arbeitsblatt, in dem du die #REF Fehler finden möchtest.

  2. Erstelle einen Button, um den VBA-Code auszuführen. Gehe zu "Entwicklertools", wähle "Einfügen" und platziere einen Button auf deinem Arbeitsblatt.

  3. Füge den folgenden VBA-Code ein. Dieser Code sucht nach allen #REF Fehlern in der Spalte 2 und löscht die Zeilen, in denen diese Fehler auftreten:

    Private Sub UpdateButton_Click()
   Dim iRow As Long
   Dim i As Long
   With ThisWorkbook.Worksheets("MGMT-Overview")
       iRow = .Cells(.Rows.Count, 12).End(xlUp).Row
       'Färbe den wichtigen Inhalt
       .Range("F4:H" & CStr(iRow)).Interior.Color = RGB(214, 220, 228)
       'Suche nach fehlerhaften Werten im Blatt
       For i = iRow To 4 Step -1
           If IsError(.Cells(i, 2).Value) Then
               If .Cells(i, 2).Value = CVErr(xlErrRef) Then
                   .Rows(i).Delete
               End If
           End If
       Next i
   End With
End Sub

  4. Teste den Button, um sicherzustellen, dass die Zeilen mit dem #REF Fehler erfolgreich gelöscht werden.

Häufige Fehler und Lösungen

  • Fehler: Run-time Fehler '13' Type mismatch
    Lösung: Überprüfe den Code, um sicherzustellen, dass du die richtige Zeile und Spalte ansprichst. Stelle sicher, dass die Schleife von unten nach oben läuft, um Probleme beim Löschen von Zeilen zu vermeiden.

  • Fehler: Es werden nicht alle #REF Fehler erkannt
    Lösung: Vergewissere dich, dass die Prüfung auf Fehler korrekt implementiert ist. Der Code sollte sicherstellen, dass nur Zellen mit dem #REF Error überprüft werden.

Alternative Methoden

Eine alternative Methode, um #REF Fehler in Excel zu finden, ist die Verwendung von Excel-Funktionen. Du kannst die Funktion =ISTFEHLER() verwenden, um zu überprüfen, ob ein Fehler vorliegt:

=ISTFEHLER(A1)

Diese Funktion gibt "WAHR" zurück, wenn in Zelle A1 ein Fehler vorliegt.

Praktische Beispiele

Angenommen, du hast ein Overview-Sheet, das Daten von verschiedenen anderen Sheets zusammenfasst. Wenn du eine Zeile in einem der anderen Sheets löschst, kann ein #REF! Fehler im Overview-Sheet entstehen.

Mit dem oben gezeigten VBA-Code kannst du diese Fehler automatisch entfernen, sodass dein Overview-Sheet immer aktuell und frei von #REF Fehlern bleibt.

Tipps für Profis

  • Nutze die IsError Funktion: Diese Funktion hilft dir, unerwartete Fehler zu vermeiden, indem sie sicherstellt, dass du nur mit Zellen arbeitest, die tatsächlich Fehler enthalten.

  • Die Schleifenrichtung beachten: Wenn du Zeilen löschst, solltest du immer von unten nach oben iterieren, um sicherzustellen, dass du beim Löschen von Zeilen keine Zeilen überspringst.

FAQ: Häufige Fragen

1. Was bedeutet der #REF Fehler in Excel?
Der #REF Fehler entsteht, wenn eine Formel auf eine Zelle verweist, die nicht mehr vorhanden ist. Dies passiert oft, wenn Zeilen oder Spalten gelöscht werden.

2. Wie kann ich #REF Fehler manuell finden?
Du kannst die "Fehlerüberprüfung" in Excel verwenden, um Zellen mit Fehlern zu identifizieren. Gehe zu "Formeln" > "Fehlerüberprüfung" und wähle "Fehler in der aktuellen Auswahl".

3. Funktioniert dieser Code in allen Excel-Versionen?
Ja, der bereitgestellte VBA-Code sollte in allen modernen Excel-Versionen funktionieren, die die Verwendung von VBA unterstützen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige