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

Doppelte Werte per VBA verhindern

Forumthread: Doppelte Werte per VBA verhindern

Doppelte Werte per VBA verhindern
Dietmar
Hallo in die Runde,
habe für meine Bedürfnisse trotz Recherche leider nichts geeignetes gefunden.
Beispiel-Sachverhalt:
Im Bereich Tabelle1!A2:A10 generiere ich per Gültigkeitsprüfung eines Namens 'Produkte' (Milch, Kaffee, Butter usw).
Die Gültigkeits-Daten stammen aus einem ausgeblendeten Sheet 'Tabelle2!A2:B10'. Im Bereich 'B2:B10' stehen dort 4-stellige ProduktCodes
Auf diese Art und Weise bin ich in der Lage mir die Zusammenstellung in Tabelle1 individuell zu gestalten. Den ProduktCode in Tabelle1!B2:10' generiere ich über einen SVerweis zu Tabelle2.
Soweit so gut. Das klappt auch alles gut.
Frage:
Nun möchte ich per VAB (wahrscheinlich Worksheet-Change ...) verhindern, dass in Tabelle!1 ein Produkt doppelt dargestellt wird.
Wenn also zB das Produkt 'Milch' in 'A2' mit dem Code 1234 in 'B2' per Gültigkeits-DropDown aufgerufen wird, soll der Code (nicht der Produktname) in Zelle 'B2' mit den restlichen Zellen 'B3:B10' abgeglichen werden.
Wenn der Code bereits vorkommt, soll eine a) msgbox erscheinen und und b) das Vorhaben neutralisiert werden.
Ich hoffe es ist nicht zu kompliziert und bin sehr gespannt.
Viele Dank vorab für jede Hilfe!
Viele Grüße
Dietmar aus Aachen
Anzeige
AW: Doppelte Werte per VBA verhindern
01.12.2011 15:00:31
Rudi
Hallo,
reicht nicht das Hervorheben per bedingter Formatierung?
Gruß
Rudi
AW: Doppelte Werte per VBA verhindern
01.12.2011 15:59:50
Dietmar
Hallo Rudi,
vielen Dank für Dein Feedback.
Nein eine bedingte Formatierung reicht leider nicht, da ich in meiner O-Datei an diesen Stellen schon genügend bedingte Formatierungen verwende. (Mein geschildertes Konstrukt diente ja nur der Erläuterung).
Außerdem MUSS verhindert werden, dass ein Doppeleintrag stattfindet, da "meine Endeanwender" auf bed. Format. reagieren müssten, dies aber nach meinen Erfahrungen nicht zuverlässig unterstellt werden darf.
Hoffe daher auf eine VBA-Lösung.
Viele Grüße
Dietmar
Anzeige
AW: Doppelte Werte per VBA verhindern
01.12.2011 17:20:31
serge
Hallo Dietmar
mit der gültigkeit in a1: Daten Gültigkeit benutzerdefiniert/ Formel =ZÄHLENWENN($A$1:A14;A1)=1
und herunterkopieren
Serge
Beispielmappe ?
01.12.2011 19:22:33
Erich
Hi Dietmar,
bei dieser Frage möchte ich dir empfehlen, eine BeiSpielMappe hochzuladen,
mit den bisherigen Gültigkeitsprüfungen, Produktcodes und SVERWEISen.
Das erleichtert uns die Arbeit und beschert dir eher eine passende Lösung,
ob per VBA oder erweiterter Gültigkeitsprüfung.
Ich sehe aus deiner Beschreibung nicht, ob derzeit die Produktcodes in der Gültigkeitsprüfung eine Rolle spielen.
Da werden doch jetzt nur die Produktnamen geprüft, oder?
(ass künftig auch geprüft werden soll, dass kein Produktcodes doppelt ist, ist klar.)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige
Hier die Beispielmappe
01.12.2011 23:10:41
Dietmar
Hallo Ericht,
vielen Dank. Du hast natürlich recht, so geht es besser.
Nachfolgend die Beispieldei
Im VBA-Bereich findest Du auch meinen untauglichen Versuch (da hatte ich noch alles in Tabelle 1 liegen).
Herzlichen Dank
Dietmar aus Aachen
https://www.herber.de/bbs/user/77779.xls
Anzeige
Rückfrage
02.12.2011 08:43:20
Erich
Hi Dietmar,
in deiner Mappe kommt die Komplikation, derentwegen du die Frage stellst, nicht vor, oder habe ich das übersehen?
Zur Klärung diese Tabelle:
 ABC
1PreislisteCodePreis
2Kuhmilch1111,00 €
3Milch1111,00 €
4Milch1231,00 €
5Zucker2222,00 €
6Brot3333,00 €
7Butter4444,00 €
8Bohnen5555,00 €
9Erbsen6666,00 €
10Tomaten7777,00 €
11Bananen8888,00 €
12unbelegt19910,00 €
13unbelegt29920,00 €

Was kommt in deinen Daten vor:
a) es gibt zumindest einen Code (hier 111) mit mehreren Produktbezeichungen (Milch + Kuhmilch)
b) es gibt zumindest ein Produkt (hier Milch) mit mehreren Codes (111 + 123)
c) oder gibt es sogar beides, a) und b) ?
Wie auch immer, lässt sich das nicht generell bereinigen, so dass dein Problem gar nicht erst entsteht?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige
Klarstellung ausführlich, wie es gemeint ist
02.12.2011 10:28:14
Dietmar
Hallo Erich,
vielen Dank für Dein ausführliches und tolles Feedback.
Folgendes zur Klarstellung:
a) Die Basis-Daten befindet sich im Tabellenblatt 2, das normalerweise ausgeblendet ist,
Hier sollen alle verfügbaren Produkte erfasst werden.
Damit dort keine doppelten Codes erfasst werden, werde ich das DORT mit der benutzer-
definierten Gültigkeitsprüfung 'Zählenwenn($B$2:B12;B2)=1' für meine Datenpflege ab-
sichern.
Der Bereich Tabelle2!A2:A11 hat den Namen 'Produkte'
_
b) So, und nun soll Tabelle 1wie folgt funktionieren:
1.) A2:A11 greift über die Gültigkeitsprüfung auf den Namen 'Produkte' zu, so dass dort nur die
auf Tabelle2 aufgeführten Produkte abgreifbar sind. Z.B. Milch.
2.) B2 (z.B.) generiert aufgrund den gewähten Produktes A2 den Code per SVerweis.
3.) Wenn ich jetzt in (z.B.) A3 auch das Produkte von A2 (Milch) auswählen würde, wurde ja in B3 der
gleiche Code genierte wie in B2.
Und genau DAS soll per VBA verhindert werden, d.h. der Vorgang soll nach Warnhinweis neutralisiert
werden.
_
c) Führend ist immer der Code, ich werde in der (eigentlich) ausgeblendeten Tabelle 2 dafür sorgen,
dass kein Produkt und kein Code dopptelt erscheint.
Mein Absicherungsbedürfnis liegt in Tabelle 1.
Ich hoffe, dass ich der Vorgang etwas transparenter machen konnte.
Bis total gespannt, ob es eine Lösung gibt :-)
Viele Grüße
Dietmar aus Aachen
Anzeige
nochmals Klarstellung
02.12.2011 10:45:13
Dietmar
Hallo Erich,
eine kleine Ergänzung noch zu meiner Erläuterung von eben:
Codes dürfen deshalb nicht doppelt vorkommen, weil meine recht komplexe Originaldatei diese Daten 'in der Tiefe' für die Lagerhaltung verwendet. Ich möchte dies durch diese neue Idee weiterentwickeln.
Ein Doppelter Artikel-Code wurde dort durcheinander produzieren.
Viele Grüße
Dietmar aus Aachen
Anzeige
Lösungsvorschlag
02.12.2011 11:03:57
Rudi
Hallo,
ins Modul der Tabelle: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'Änderung in A
On Error GoTo ERRHANDLER
Application.EnableEvents = False
If Target  "" Then  'nicht leer
If Application.CountIf(Columns(1), Target) > 1 Then
'doppelt --> löschen
Target.Resize(, 2) = ""
Target.Activate
Else
'einfach --> B eintragen
Target.Offset(, 1) = _
Application.VLookup(Target, Sheets("Basis").Range("A:B"), 2, 0)
End If
Else
'leer --> B löschen
Target.Offset(, 1) = ""
End If
End If
ERRHANDLER:
Application.EnableEvents = True
End Sub

Gruß
Rudi
Anzeige
guter Ansatz, aber ...
02.12.2011 11:49:06
Dietmar
Hallo Rudi,
Herzlichen Dank!
vom Ansatz her klasse!
Erkenne es leider immer erst, wenn ich den Code sehe; d.h. proaktives Erstellen fällt mir noch schwer :-)
Leider zerstört der VBA-Code mir den SVerweis in Spalte B
Bei der Feststellung, dass der Code schon vorkommt, müsste der Vorgang neutralisisert werden. Also zurücksetzen in der vorigen erlaubten Zustand.
Mit dem Löschvorgang in der Spalte B gehts leider nicht.
Aber ich glaube, dass Du ganz nah dran bist.
Viele Grüße aus Aachen
Dietmar
Anzeige
Mein Gott, jetzt hat er's wahrscheinlich :-)
02.12.2011 12:01:34
Dietmar
Hallo Rudi,
mein Vorschlag, Deinen Code etwas zu ändern:
Habe in 'doppelt löschen' Target.Resize(, 1) anstatt (, 2) genommen.
Jetzt geht es :-)
Ich glaube, dass das die Lösung ist, oder?
1000 Dank für kurzes Feedback, denn dann würde ich das in meine O-Datei einarbeiten.
Viele Grüße
Dietmar
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'Änderung in A
On Error GoTo ERRHANDLER
Application.EnableEvents = False
If Target  "" Then  'nicht leer
If Application.CountIf(Columns(1), Target) > 1 Then
'doppelt --> löschen
Target.Resize(, 1) = ""
Target.Activate
Else
'einfach --> B eintragen
Target.Offset(, 1) = _
Application.VLookup(Target, Sheets("Basis").Range("A:B"), 2, 0)
End If
Else
'leer --> B löschen
'Target.Offset(, 1) = ""
End If
End If
ERRHANDLER:
Application.EnableEvents = True
End Sub

Anzeige
AW: Mein Gott, jetzt hat er's wahrscheinlich :-)
02.12.2011 12:25:35
Rudi
Hallo,
.resize(, 1) kannst du ganz löschen.
Mein Ansatz war, dass du, wenn du ohnehin VBA einsetzt, ja auch auf die Formel verzichten kannst und statt dessen den Wert in B per VBA einfügst.
Kleine Verfeinerung, mit der B nicht mehr angerührt wird: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'Änderung in A
On Error GoTo ERRHANDLER
Application.EnableEvents = False
If Target  "" Then  'nicht leer
If Application.CountIf(Columns(1), Target) > 1 Then
'doppelt --> rückgängig
Application.Undo
Target.Activate
End If
End If
ERRHANDLER:
Application.EnableEvents = True
End Sub

Gruß
Rudi
Anzeige
Das ist elegant :-) Danke ...
02.12.2011 13:28:22
Dietmar
Hallo Rudi,
mit dem 'undo' ist cool! (irgendsowas schwebe mir auch vor)
Jetzt ist es PERFEKT!
Mal was für die Verantwortlichen dieses Forums:
Ich fühle mich hier im Forum total wohl und habe immer tolle Lösungen bekommen.
Das schöne ist auch, dass man das Gefühl hat, hier richtig ernst genommen zu werden und dass sich
immer jemand intenisv kümmert.
Gib's mal irgendwie weiter, denn Du scheinst doch zum engeren Kreis zu gehören :-)
Besten Dank jedenfalls und eine schöne Vorweihnachtszeit!
Viele Grüße
Dietmar
Anzeige
doch noch ein kleiner Ergänzungswunsch
02.12.2011 13:36:19
Dietmar
Hallo Rudi,
wie müsste der Code lauten, wenn NUR der Begriff 'unbelegt' in Spalte A wiederholt vorkommen dürfte.
Danke.
Gruß Dietmar
ich glaube ich hab's, schau mal bitte
02.12.2011 13:42:55
Dietmar
Hallo Rudi,
ich glaube ich hab's:
If Target  "" And Target  "unbelegt" Then  'nicht leer oder 'unbelegt'
Oder doch noch anders?
Jedenfalls läut's
Gruß Dietmar
Anzeige
schau mal bitte
02.12.2011 13:56:38
Rudi
Hallo,
wenn's läuft, ist doch gut.
Gruß
Rudi
;

Forumthreads zu verwandten Themen

Keine doppelten Werte
Claudia

Guten Morgen,
sagt mal bitte, wie geht man hier am besten vor?
Ich habe eine Spalte in der pro Zeile jeweils ein Wort steht. Manche dieser Wörter sind jedoch identisch/ kommen doppelt vor etc.. Wie kann ich (z.B. in der Nachbarspalte) eine Liste erzeugen, in der untereinander und ohne Z...
03.04.2013 09:04:55
Thread-ID: 1306984
4 Beiträge
keine doppelten Werte eintragen!
Snewi

Hallo,
ich hab mehrere Textfelder, von diesen der Inhalt bei Übernahme einfach immer untereinander in die nächste freie Zeile eingetragen wird!
Wenn jetzt in den Textfeldern das gleiche drin stehen würde wie in der Tabelle schon vorhanden, würde trotzdem eingetragen!
Wie kann ich es...
24.10.2013 10:31:37
Thread-ID: 1335689
8 Beiträge
SQL Abfrage keine doppelten Werte
Silent_Warrior5

Hallo Zusammen,
habe eine SQL Abfrage einer andern Excel Datei gebaut, klappt auch gut.
Nun habe ich die "SELECT DISTINCT" Anweisung gefunden, um doppelte herauszufiltern.
Problem ist ich würde gerne z.b. Datum und Wochentag usw in der Zeile lassen. Bekomme das net hin.
so sieht...
26.12.2013 18:23:33
Thread-ID: 1343542
2 Beiträge
Keine doppelten Werte in den String schreiben
Fredi

Hallo zusammen,
ich habe ein kleines Problem mit meinem Makro.
Ich versuche derzeit ein E-Mail-Verteiler zu erstellen.
Wenn in der Spalte "D" eine leere Zelle gefunden wird, wird in der gleichen Zeile die Spalte "B" in ein String geschrieben (hier steht die E-Mail)
Bis dahin kla...
10.12.2015 14:38:12
Thread-ID: 1463602
4 Beiträge
Keine doppelten Werte zählen
Flo

Halllo an alle
Ich finde leider keine passende Formel die funktioniert.
Ich habe ein Bereich von A3:O100
Hier stehen verschiedene Texte und Zahlen drin
Beispiel:
A5 - ems_12
D50 - ems_9
I13 - ems_12
I15 - ems_11
Ich möchte jetzt gerne die Anzahl der „ems“ z...
03.04.2019 19:41:24
Thread-ID: 1685006
11 Beiträge
keine doppelten Werte eingeben
Thorsten
Hallo Forumsteilnehmer,

bestimmt kann mir einer von Euch helfen.
Ich möchte verhindern, das in Spalte A eines Tabellenblattes doppelte Werte eingegeben bzw reinkopiert werden können.

Hat jemand dafür eine Lösung?

Vorab schon mal besten Dank.

Mfg
Thorsten
18.12.2003 12:12:35
Thread-ID: 353820
4 Beiträge
keine Doppelten Einträge zulassen
simon

Hallo Zusammen,
wurde bestimmt schon öfters gefragt, aber irgendwie fand ich im Archiv nichts.
Gebe über eine UF Zahlen ein, diese werden in Tabelle1 Spalte A gespeichert,
ich möchte jetzt verhindern dass doppelte eingaben gemacht werden können. Also
z.B. wenn die Zahl 100 schon...
07.12.2004 22:33:23
Thread-ID: 529556
8 Beiträge
Anzeige
Auswahlliste keine doppelten Einträge zulassen
Peter

Ich möchte für die Fahrzeugeinteilung bei der Feuerwehr in einer Zelle z.B. A2 eine Auswahlliste mit allen im Dienst befindlichen Kollegen erstellen. Das kann ich schon, nur sollte der Kollege wenn er einmal auf ein Fahrzeug zugeteilt wurde für eine weitere Auswahl in der Liste nicht mehr verfü...
23.09.2006 08:07:57
Thread-ID: 804743
3 Beiträge
Keine doppelten Werte in neue Spalte
Philipp
Hallo, wie kann ich es machen dass in einer Spalte in der mehrere Werte öfters vorkommen der erste Wert nur in die nächste Spalte übernommen wird?
Beispiel Spalte C:
C2: 20042
C3: 20042
C4: 20042
C5: 20045
C6: 20046
C7: 20046
C8: 20057
C9: 20061
Spalte D soll...
14.06.2021 12:16:52
Thread-ID: 1835041
4 Beiträge
keine doppelten Werte
Daniela
Guten Tag zusammen,
Ich erstelle gerade ein File mit Bestellungen von 10 verschiedenen Lieferanten. Das Problem ist aber, pro versendete Bestellung und Tagesdatum darf nur jeweils 1 ganzer Datensatz in die Datenbank pro Lieferant eingetragen werden.
Im folgenden Makro habe ich es mal versuc...
04.07.2023 09:47:07
Thread-ID: 1935385
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Doppelte Werte in Excel per VBA verhindern

Schritt-für-Schritt-Anleitung

  1. Öffne die Excel-Datei und gehe zum VBA-Editor:

    • Drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Finde das entsprechende Arbeitsblatt:

    • Doppelklicke im Projektfenster auf das Arbeitsblatt, in dem du die doppelten Werte in Excel verhindern möchtest (z.B. Tabelle1).

  3. Füge den folgenden VBA-Code ein:

    Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 Then 'Änderung in Spalte A
       On Error GoTo ERRHANDLER
       Application.EnableEvents = False
       If Target <> "" Then  'nicht leer
           If Application.CountIf(Columns(1), Target) > 1 Then
               'doppelt -- rückgängig machen
               Application.Undo
               MsgBox "Doppelte Werte sind nicht erlaubt!", vbExclamation
               Target.Activate
           Else
               ' Hier kannst du den SVerweis einfügen, um den Code in Spalte B zu setzen
               Target.Offset(, 1) = Application.VLookup(Target, Sheets("Basis").Range("A:B"), 2, 0)
           End If
       End If
   End If
ERRHANDLER:
   Application.EnableEvents = True
End Sub

  4. Schließe den VBA-Editor und speichere die Datei als Makro-fähige Excel-Datei (*.xlsm).

  5. Teste die Eingaben:

    • Gib in Spalte A Werte ein und überprüfe, ob die Eingabe von doppelten Werten in Excel blockiert wird.

Häufige Fehler und Lösungen

  • Fehler: Der VBA-Code funktioniert nicht.

    • Lösung: Stelle sicher, dass der Code im richtigen Arbeitsblattmodul eingefügt wurde. Der Code muss in dem Blatt liegen, wo die Eingaben stattfinden.

  • Fehler: Die MsgBox erscheint, aber der Wert bleibt trotzdem in der Zelle.

    • Lösung: Überprüfe, ob der Code Application.EnableEvents = False korrekt gesetzt ist, um zu verhindern, dass der Code erneut ausgelöst wird, während er selbst läuft.

  • Fehler: SVerweis gibt einen Fehler zurück.

    • Lösung: Stelle sicher, dass die Daten im Arbeitsblatt "Basis" korrekt formatiert und vorhanden sind.

Alternative Methoden

  • Bedingte Formatierung: Du kannst auch die bedingte Formatierung verwenden, um doppelte Werte hervorzuheben. Dies ist jedoch nicht so effektiv, wenn du die Eingabe doppelter Werte vollständig verhindern möchtest.

  • Gültigkeitsprüfung: Eine einfache Gültigkeitsprüfung kann genutzt werden, um sicherzustellen, dass nur bestimmte Werte eingegeben werden können. Dies kann jedoch nicht die Eingabe doppelter Werte verhindern.

Praktische Beispiele

  • Beispiel 1: Wenn du in Spalte A der Tabelle "Produkte" Milch eingibst, und danach erneut Milch in eine andere Zelle eingibst, wird der Code die Eingabe blockieren und eine Warnmeldung anzeigen.

  • Beispiel 2: Der SVerweis in der zweiten Spalte bezieht den Produktcode aus einer anderen Tabelle. Wenn Milch in A2 steht, wird in B2 der entsprechende Code automatisch eingetragen.

Tipps für Profis

  • Erweiterung des Codes: Du kannst den Code anpassen, um bestimmte Ausnahmen zuzulassen, z.B. wenn du nur den Begriff unbelegt mehrmals zulassen möchtest. Füge dazu eine Bedingung ein:

    If Target <> "" And Target <> "unbelegt" Then

  • Leistungsoptimierung: Reduziere die Anzahl der Berechnungen, indem du nur für bestimmte Zellen das Ereignis auslöst.

FAQ: Häufige Fragen

1. Wie kann ich doppelte Werte in Excel ausblenden? Um doppelte Werte in Excel auszublenden, kannst du die bedingte Formatierung nutzen, um sie farblich hervorzuheben.

2. Was passiert, wenn ich in der Tabelle mit doppelten Werten arbeite? Die Excel-Eingabe doppelter Werte wird durch den VBA-Code verhindert, sodass keine doppelten Einträge in der Tabelle bestehen bleiben.

3. Kann ich auch mehrere Spalten gleichzeitig prüfen? Ja, du kannst den Code anpassen, um auch andere Spalten zu überprüfen. Füge dafür weitere Bedingungen in die If-Anweisung ein.

4. Wie kann ich sicherstellen, dass Excel keine doppelten Werte zulässt? Der VBA-Code sorgt dafür, dass beim Versuch, einen doppelten Wert einzugeben, der Vorgang abgebrochen und eine Warnmeldung angezeigt wird.

Beliebteste Forumthreads (12 Monate)

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