XL2010, VBA} Komplettes Sheet schnell auf Fehlerwerte prüfen

[Jörg Eisenträger]

Hallo NG,

wie kann ich auf einem Sheet per VBA schnell prüfen, ob es fehlerhafte
Formelergebnisse wie #BEZUG, #WERT, #DIV/0 usw. gibt?
Ich möchte vermeiden, einige 10000 Zellen, alle mit Formeln, einzeln zu
durchlaufen und auf IsError abzuprüfen.
Gibt es etwas eleganteres, schnelleres? Und etwas, was evtl. auch gleich
die Fehlerzellen hervorhebt?


Gruß
Jörg

[Claus Busch]

Hallo Jörg,

Post by Jörg Eisenträger
wie kann ich auf einem Sheet per VBA schnell prüfen, ob es fehlerhafte
Formelergebnisse wie #BEZUG, #WERT, #DIV/0 usw. gibt?
Ich möchte vermeiden, einige 10000 Zellen, alle mit Formeln, einzeln zu
durchlaufen und auf IsError abzuprüfen.
Gibt es etwas eleganteres, schnelleres? Und etwas, was evtl. auch gleich
die Fehlerzellen hervorhebt?

sogar schon eingebaut:
Suchen und Auswählen => Gehe zu => Inhalte => Formeln => Fehler


Mit freundlichen Grüßen
Claus

[Jörg Eisenträger]

Post by Claus Busch

Post by Jörg Eisenträger
wie kann ich auf einem Sheet per VBA schnell prüfen, ob es fehlerhafte
Formelergebnisse wie #BEZUG, #WERT, #DIV/0 usw. gibt?

Suchen und Auswählen => Gehe zu => Inhalte => Formeln => Fehler

Hallo Claus,

diesen Dialog kannte ich noch gar nicht. Danke für den Hinweis. Da ich
das Ganze aber per VBA brauche, habe ich es aufgezeichnet und
abgewandelt zu
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select


Gruß
Jörg

[Jörg Eisenträger]

Post by Jörg Eisenträger
diesen Dialog kannte ich noch gar nicht. Danke für den Hinweis. Da ich
das Ganze aber per VBA brauche, habe ich es aufgezeichnet und
abgewandelt zu
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select

Habe doch noch ein Problem. Ich hatte erwartet, dass
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Count
die Anzahl der fehlerhaften Formelzellen ausgibt.

Macht es auch. Wenn es jedoch keine derartigen Fehlerzellen im Sheet
gibt, wird nicht etwa 0 zurückgegeben, sondern es kommt ein
Laufzeitfehler ("Keine Zellen gefunden.")

Ich überspringe den Laufzeitfehler im Moment mit On Error, aber die hohe
Schule ist das sicher nicht. Wie geht es richtig?


Gruß
Jörg

[Claus Busch]

Hallo Jörg,

Post by Jörg Eisenträger
Ich überspringe den Laufzeitfehler im Moment mit On Error, aber die hohe
Schule ist das sicher nicht. Wie geht es richtig?

du musst auf die Fehlerzellen prüfen, da kommst du nicht drum herum. Und
schon hast du deinen Laufzeitfehler. Es bleibt eigentlich nur On Error.

Oder du machst einen Workaround und zählst alle Zellen mit Formeln und
dann die Zellen ohne Fehler. Aber da ist On Error schon übersichtlicher.
Teste selbst einmal:

Sub Test()
Dim Anz1 As Long, Anz2 As Long
Dim rngC As Range

With ActiveSheet
For Each rngC In .UsedRange
If rngC.HasFormula Then
Anz1 = Anz1 + 1
End If
Next

Anz2 = .UsedRange.SpecialCells(xlCellTypeFormulas, 7).Count
End With
MsgBox IIf(Anz1 - Anz2 = 0, "Keine Zellen gefunden", Anz1 - Anz2)
End Sub


Mit freundlichen Grüßen
Claus

[Jörg Eisenträger]

Hallo Claus,

Post by Claus Busch
Sub Test()
Dim Anz1 As Long, Anz2 As Long
Dim rngC As Range
With ActiveSheet
For Each rngC In .UsedRange
If rngC.HasFormula Then
Anz1 = Anz1 + 1
End If
Next

On Error resume next

Post by Claus Busch
Anz2 = .UsedRange.SpecialCells(xlCellTypeFormulas, 7).Count
End With
MsgBox IIf(Anz1 - Anz2 = 0, "Keine Zellen gefunden", Anz1 - Anz2)
End Sub

Weil es heute vorkam, der Vollständigkeit halber:
Auch diese Sub kann zu einem RTE führen, und zwar dann, wenn alle
Formeln einen Fehlerwert liefern. Um das abzufangen, hilft hier auch nur
ein On Error, z. B. wie oben hinzugefügt.


Gruß
Jörg

[Claus Busch]

Hallo Jörg,

Post by Jörg Eisenträger
Auch diese Sub kann zu einem RTE führen, und zwar dann, wenn alle
Formeln einen Fehlerwert liefern. Um das abzufangen, hilft hier auch nur
ein On Error, z. B. wie oben hinzugefügt.

eine anständige und sinnvolle Fehlerbehandlung gehört auch zum
Programmieren.
Sie darf nur nicht zum Selbstzweck werden, dass man sagt, egal wie
schlampig ich programmiere, die Fehlerbehandlung bügelt das schon aus.


Mit freundlichen Grüßen
Claus

[Claus Busch]

Hallo Jörg,

Post by Jörg Eisenträger
Auch diese Sub kann zu einem RTE führen, und zwar dann, wenn alle
Formeln einen Fehlerwert liefern. Um das abzufangen, hilft hier auch nur
ein On Error, z. B. wie oben hinzugefügt.

berechne eben beide Möglichkeiten mit Fehlerbehandlung:

Sub FehlerZaehlen()
Dim Anz As Long

On Error Resume Next
With ActiveSheet.UsedRange
'Anzahl Formeln mit Fehler
Anz = .SpecialCells(xlCellTypeFormulas, 16).Count
'Anzahl Formeln gesamt - Anzahl Formeln korrekt
Anz = .SpecialCells(xlCellTypeFormulas, 23).Count _
- .SpecialCells(xlCellTypeFormulas, 7).Count
End With

MsgBox Anz
End Sub


Mit freundlichen Grüßen
Claus