#NV löschen

Forumthread: #NV löschen

#NV löschen

08.06.2022 09:04:56

Sven

Hallo,
ich habe ein SVerweis Sub mittels "Makro aufzeichnen" erstellt.
Nun habe ich in den Zellen, in denen kein Wert verfügbar ist ein #NV stehen.
Wollte das über folgendes Makro eleminieren:
Cells.Replace What:="#NV", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False, FormulaVersion:=xlReplaceFormula2
Leider funktioniert es nicht wie erhofft.
Wenn ich es händisch über Strg+F -> alle ersetzten mache, fliegen die #NVs raus.
Kann mir jmd sagen, woran das liegt?
Danke vorab & Gruß,
Sven

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

VBA spricht englisch...

08.06.2022 09:10:21

{Boris}
AW: #NV löschen

08.06.2022 09:10:44

Hardy
AW: #NV löschen

08.06.2022 09:24:49

Sven
AW: #NV löschen

08.06.2022 10:10:43

Daniel

VBA spricht englisch...

08.06.2022 09:10:21

{Boris}

Hi,


Kann mir jmd sagen, woran das liegt?

#NV ist im Englischen #N/A - danach musst Du per VBA suchen.
Aber weshalb unterbindest Du die #NV nicht gleich mit der Formel?
=WENNFEHLER(SVERWEIS(...);"")
VG; Boris

AW: #NV löschen

08.06.2022 09:10:44

Hardy

Hallo Sven,
es gibt zwei Möglichkeiten, packe um deine Formel ein =Wennfehler(....) oder mache eine Bedingte Formatierung und stelle als Schriftfarbe für #NV die Hintergrundfarbe ein.
Gruß
Hardy R

AW: #NV löschen

08.06.2022 09:24:49

Sven

Danke Boris und Hardy R für die schnelle Antwort!
Das mit #N/A wusste ich nicht. Wieder was gelernt.
Funktioniert sogar! Merci
Die WennFehler-Methode ist sicherlich die schickere.
Werde ich auch mal testen.
Gruß,
Sven

AW: #NV löschen

08.06.2022 10:10:43

Daniel

Hi
es gäbe noch eine Möglichkeit, die Zellen mit Fehler zu identifizieren und zu bearbeiten.
hierzu geht man über das START - BEARBEITEN - SUCHEN UND AUSWÄHLEN - INHALTE und kann dann die Selektion auf Zellen mit Fehlern einschränken, um diese dann zu leeren.
Dabei muss man nur noch entscheiden, ob man noch Formeln hat, oder ob man die Formeln schon durch Werte ersetzt hat.
in VBA sieht das dann so aus:


Cells.SpecialCells(xlcelltypeconstants, 16).ClearContents

dabei steht die 16 dann für Zellen mit Fehlerwert.
"xlcelltypeconstants" nimmt man dann, wenn man schon die Fixwerte hat
"xlcelltypeformulas" nimmt man, wenn man noch Formeln in den Zellen stehen hat.
diese Methode hat allerdings den nachteil, dass sie einen Fehlerabbruch erzeugt, wenn es keine entsprechende Zellen gibt, dh für deinen Fall, wenn alle SVerweise ein Ergebnis liefern.
daher sollte man das so erweitern, damit der Fehler nicht durchschlägt:


On Error Resume Next
Cells.SpecialCells(xlcelltypeconstants, 16).ClearContents
On Error Goto 0

wobei ich es auch bevorzugen würde, das Problem bei der Wurzel zu packen und den SVerweis mit WennFehler zu erweitern, so wie es die Kollegen schon vorgeschlagen haben.
Gruß Daniel

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um das lästige #NV in Excel zu entfernen, kannst du folgende Schritte ausführen:

Formel anpassen: Um das #NV direkt bei der Formel zu vermeiden, kannst du die WENNFEHLER-Funktion verwenden:
```
=WENNFEHLER(SVERWEIS(...);"")
```
Mit dieser Methode wird anstelle des #NV ein leerer Wert angezeigt.
Makro verwenden: Wenn du bereits eine Formel hast und das #NV nachträglich entfernen möchtest, kannst du ein Makro verwenden:
```
Sub NV_Entfernen()
   Cells.Replace What:="#NV", Replacement:="", LookAt:=xlPart, MatchCase:=False
End Sub
```
Dieses Makro ersetzt alle #NV-Werte durch leere Zellen.
Fehlerhafte Zellen identifizieren: Du kannst auch gezielt Zellen mit Fehlern suchen und bearbeiten. Gehe dazu auf:
- Start > Bearbeiten > Suchen und Auswählen > Inhalte und wähle „Zellen mit Fehlern“ aus, um diese zu leeren.

Häufige Fehler und Lösungen

Fehler beim Makro: Wenn das Makro nicht funktioniert, überprüfe, ob die Schreibweise von #NV korrekt ist. Im Englischen wird #N/A verwendet. Achte darauf, dass du das richtige Wort verwendest.
Kein Ergebnis nach Ersetzung: Wenn das Makro keine #NV-Werte entfernt, könnte es daran liegen, dass die Werte nicht als Text erkannt werden. Überprüfe die Zellformatierung.
Leere Zellen bleiben bestehen: Wenn du leere Zellen hast, die nicht entfernt werden, nutze die WENNFEHLER-Funktion, um die Fehler bereits in der Formel zu vermeiden.

Alternative Methoden

Bedingte Formatierung: Du kannst die Schriftfarbe für #NV so einstellen, dass sie der Hintergrundfarbe entspricht, wodurch die Werte unsichtbar werden.
VBA zur Fehlerbehandlung: Nutze VBA, um alle Fehler in einem Bereich zu löschen:
```
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, 16).ClearContents
On Error GoTo 0
```
Dies entfernt Zellen mit Fehlerwerten, ohne einen Abbruch zu verursachen.
Excel NV ausblenden: Um #NV in Excel auszublenden, kannst du die Zellen formatieren oder mit VBA arbeiten.

Praktische Beispiele

Anwendung der WENNFEHLER-Funktion:
```
=WENNFEHLER(SVERWEIS(A1;B:C;2;FALSCH);"")
```
Hier wird der SVERWEIS verwendet, und im Falle eines Fehlers wird ein leerer Wert zurückgegeben.
Makro zur Entfernung von #NV: Erstelle ein neues Modul in Excel und füge folgenden Code ein:
```
Sub NV_Entfernen()
   Cells.Replace What:="#NV", Replacement:="", LookAt:=xlPart
End Sub
```

Tipps für Profis

Verwende die WENNFEHLER-Funktion nicht nur für SVERWEIS, sondern auch für andere Formeln, um #NV zu vermeiden.
Teste dein Makro in einer Kopie deiner Datei, um Datenverluste zu vermeiden.
Vermeide es, alle Formeln aus Excel zu entfernen, ohne vorher eine Sicherungskopie zu erstellen.

FAQ: Häufige Fragen

1. Wie kann ich #NV in einer Formel verhindern?
Verwende die Funktion WENNFEHLER, um Fehlerwerte direkt in der Formel abzufangen.

2. Was mache ich, wenn mein Makro nicht funktioniert?
Überprüfe die Zellformatierung und stelle sicher, dass die Schreibweise korrekt ist (z.B. #NV vs. #N/A).

3. Gibt es eine Möglichkeit, alle #NV-Werte auf einmal zu entfernen?
Ja, du kannst das Makro verwenden, um alle #NV-Werte in deinem Arbeitsblatt zu ersetzen oder zu löschen.

4. Wie kann ich Zellen mit Fehlern schnell finden?
Nutze die Funktion „Suchen und Auswählen“ in Excel, um Zellen mit Fehlern zu identifizieren und zu bearbeiten.

Beliebteste Forumthreads (12 Monate)

Forumthread: #NV löschen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

NV in Excel effektiv entfernen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread