Bei Eingabe der PLZ Ort finden und umgek

Forumthread: Bei Eingabe der PLZ Ort finden und umgek

14.04.2020 21:54:22

Sabrina

Hallo Leute,
in einer Zelle (C5) kann die Postleitzahl eingegeben werden.
Dann wird in Zelle D5 der Ort ausgegeben werden.
Hat eine PLZ mehrere Orte wird eine Userform zur Auswahl ausgegeben.
Bei einem Teffer oder bei Auswahl soll dann der Ort in D5 eingetragen werden.
Wenn der Ort eingegeben wird soll das genauso funktionieren.
Und bei Löschen der Zellen soll nicht passieren
Ich habe das ganze mal mit Infos aus dem Forum zusammengeschrieben.
Aber es funktionert nicht so dolle.
Kann mir jemand Helfen?
Hier eine Testdatei
https://www.herber.de/bbs/user/136728.zip
(Aufgrund der Größe der Ort Datei sind nur ein teil der Orte in der Testdatei)
LG
Sabbel

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Bei Eingabe der PLZ Ort finden und umgek

15.04.2020 08:44:58

Regina

Hi,
was heißt "funktioniert nicht so dolle"? Etwas genauer sollte die Fehlerbeschreibung schon sein.
Gruß Regina

AW: Bei Eingabe der PLZ Ort finden und umgek

15.04.2020 09:10:19

fcs

Hallo Sabbel,
wenn Werte in die beien Eingabezellen eingetragen werden, dann müssen die Ereignismakros vorübergehend deaktiviert werden - es kommt sonst zu Rückkopplungen und wiederholten aufrufen der Makros.
Zusätzlich hab ich den Variablentyp der beiden Public Variablen auf Integer geändert, denn du weist hier ja immer Zahlen zu keine Texte.
LG
Franz
Hier deine Datei PLZ_Eingabe.xlsm mit Anpassungen.
https://www.herber.de/bbs/user/136733.xlsm

Danke .. funktioniert bestens .. weitere Option

15.04.2020 18:59:04

Sabrina

Hallo Franz,
vielen Lieben Dank.
Dank deiner Optimierung funktioniert es jetzt bestens.
Bis auf eine kleine Sache.
Wenn dass funktionieren würde wäre es Mega.
Wenn man davon ausgeht dass die PLZ und der Ort in den beiden Feldern steht.
Und man eine von beiden löscht sollte keine Msg Box geöffnet werden sondern die andere Zelle ebenfalls gelöscht (leer) werden.
Ganz liebe Grüße Sabbel

AW: Danke .. funktioniert bestens .. weitere Option

15.04.2020 20:48:53

Sabrina

Ich habe es versucht so zu lösen .. es funktioniert auch.
Ist das okay so oder muss man es anders machen?

Private Sub Worksheet_Change(ByVal Target As Range)
' Ort suche
Dim arr As Variant
Dim iCounter, Zähler As Long
Application.EnableEvents = False
If Not Target.Address(0, 0) = "PLZ_Eingabe" Then
If Not Intersect(Target, Range("PLZ_Eingabe")) Is Nothing Then
If Ort_wahl = 3 Then
GoTo Beenden
Else
PLZ_wahl = 3
UF1.ListBox1.Clear
arr = Workbooks("PLZ.xlsm").Worksheets("ORT").Range("B2").CurrentRegion.Value
For iCounter = 1 To UBound(arr)
If UCase(arr(iCounter, 2)) = UCase(Target.Text) Then
UF1.ListBox1.AddItem arr(iCounter, 3)
Zähler = Zähler + 1
End If
Next
Select Case Zähler
Case 0
If Range("Ort_Eingabe") > "" Then
Range("D6").ClearContents
GoTo Beenden
End If
MsgBox " Diese PLZ existiert nicht"
Range("C6").ClearContents
GoTo Beenden
Case 1
Range("Ort_Eingabe").Value = UF1.ListBox1.List
Case Else
UF1.Show
End Select
PLZ_wahl = 1
End If
End If
End If
' PLZ suche
If Not Intersect(Target, Range("Ort_Eingabe")) Is Nothing Then
If Ort_wahl = 3 Then
Exit Sub
Else
Ort_wahl = 3
UF2.ListBox1.Clear
arr = Workbooks("PLZ.xlsm").Worksheets("ORT").Range("A2").CurrentRegion.Value
For iCounter = 1 To UBound(arr)
If UCase(arr(iCounter, 1)) = UCase(Target.Text) Then
UF2.ListBox1.AddItem arr(iCounter, 2)
Zähler = Zähler + 1
End If
Next
Select Case Zähler
Case 0
If Range("PLZ_Eingabe") > "" Then
Range("C6").ClearContents
GoTo Beenden
End If
MsgBox " Dieser Ort existiert nicht"
Range("D6").ClearContents
GoTo Beenden
Case 1
Range("PLZ_Eingabe").Value = UF2.ListBox1.List
Case Else
UF2.Show
End Select
Ort_wahl = 1
End If
End If
Beenden:
Application.EnableEvents = True
End Sub

AW: Danke .. funktioniert bestens .. weitere Option

15.04.2020 21:53:18

fcs

Hallo Sabbel,
wenn etwas funktioniert - dann ist es doch immer ein Erfolgserlebnis.
Ich denke du hast für die Anpassungen die optimalen Positionen im Makro gefunden.
LG
Franz

AW: Bei Eingabe der PLZ Ort finden und umgek

15.04.2020 10:18:32

volti

Hallo Sabrina,
alternativ zu Deinem Ansatz ginge auch eine völlig andere Version ohne Listbox.
Man könnte bei Mehrfachfunden auch DropDowns direkt auf dem Eingabeblatt verwenden.
In der anliegenden Datei wird wechselweise auch nach Sternchen-Funktionalität (Teilangaben PLZ/Ort) gesucht. In den Daten ist das doppelte Vorhalten der Orte nicht mehr nötig, so dass die Datei auch etwas kleiner wird.
Ich habe das Datenblatt hier mal mit reingenommen.
Das ist nur ein Vorschlag und ggf. auch noch nicht ausreichend getestet....
PLZ_Eingabe.xlsm
viele Grüße
Karl-Heinz

Infobox / Tutorial

PLZ und Ort automatisch in Excel zuweisen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um in Excel die Postleitzahl (PLZ) automatisch mit dem entsprechenden Ort zu verknüpfen, folge diesen Schritten:

Daten vorbereiten: Erstelle eine Excel-Tabelle mit zwei Spalten: eine für die PLZ und eine für die zugehörigen Orte.

Beispiel:
```
| PLZ  | Ort        |
|------|------------|
| 10115| Berlin     |
| 20095| Hamburg    |
| 80331| München    |
```
Eingabefelder erstellen: Füge zwei Eingabefelder in deine Excel-Datei ein, z. B. in Zelle C5 für PLZ und D5 für den Ort.
Makro aktivieren: Du benötigst VBA (Visual Basic for Applications), um die PLZ automatisch zu erkennen und den Ort auszugeben. Gehe zu "Entwicklertools" > "Visual Basic".

Code einfügen: Füge folgenden VBA-Code in das "ThisWorkbook" oder das entsprechende Arbeitsblatt ein:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C5")) Is Nothing Then
       Dim plz As String
       plz = Range("C5").Value
       ' Hier eine Dummy-Funktion zur PLZ-zu-Ort-Zuordnung
       If plz = "10115" Then
           Range("D5").Value = "Berlin"
       ElseIf plz = "20095" Then
           Range("D5").Value = "Hamburg"
       Else
           Range("D5").Value = "Unbekannt"
       End If
   End If
End Sub

Testen der Funktion: Gib eine PLZ in Zelle C5 ein und überprüfe, ob der Ort korrekt in D5 ausgegeben wird.

Häufige Fehler und Lösungen

Fehler: Die Zelle D5 bleibt leer.
- Lösung: Überprüfe, ob die Eingabezelle C5 korrekt verbunden ist und ob der VBA-Code aktiv ist.
Fehler: Mehrere Orte für eine PLZ.
- Lösung: Implementiere eine Userform zur Auswahl, falls eine PLZ mehreren Orten zugeordnet ist.
Fehler: Fehlermeldungen beim Löschen der Eingaben.
- Lösung: Stelle sicher, dass das Makro auch auf Löschvorgänge reagiert und entsprechende Zellen leert.

Alternative Methoden

Dropdown-Listen: Anstatt einer Userform kannst Du Dropdown-Listen verwenden, um bei Mehrfachfunden die Auswahl zu erleichtern.
SVERWEIS-Funktion: Nutze die SVERWEIS-Funktion in Excel, um PLZ und Ort zu verknüpfen, ohne VBA:
```
=SVERWEIS(C5; A2:B100; 2; FALSCH)
```
Kombination mit Datenbank: Importiere eine externe Datenbank mit PLZ und Orten und nutze diese für die Suche.

Praktische Beispiele

Beispiel 1: Wenn Du eine PLZ wie "80331" eingibst, sollte automatisch "München" ausgegeben werden.
Beispiel 2: Bei einer PLZ wie "20095" sollte "Hamburg" erscheinen.

Diese Beispiele zeigen, wie einfach es ist, die PLZ automatisch einem Ort zuzuordnen, indem Du die oben genannten Schritte befolgst.

Tipps für Profis

Datenvalidierung: Setze Datenvalidierungen ein, um sicherzustellen, dass nur gültige PLZ eingegeben werden.
Fehlerprotokoll: Führe ein Protokoll über Fehler oder nicht gefundene Orte, um die Datenbank kontinuierlich zu verbessern.
Makros optimieren: Deaktiviere Ereignismakros während der Eingabe, um Rückkopplungen zu vermeiden.

FAQ: Häufige Fragen

1. Was ist eine PLZ? Die PLZ (Postleitzahl) ist eine numerische Kennzeichnung, die geografische Gebiete zur vereinfachten Postzustellung identifiziert.

2. Wie kann ich PLZ und Ort automatisch verknüpfen? Durch die Verwendung von VBA-Makros oder der SVERWEIS-Funktion kannst Du die PLZ automatisch einem Ort zuweisen.

3. Was muss man bei der PLZ-Eingabe beachten? Stelle sicher, dass die PLZ in einem gültigen Format eingegeben wird, um die Zuordnung zu ermöglichen.

4. Wie kann ich mehrere Orte für eine PLZ verwalten? Implementiere eine Auswahlmöglichkeit über eine Userform oder Dropdown-Listen, um Mehrfachfunde zu verwalten.

Beliebteste Forumthreads (12 Monate)

Bei Eingabe der PLZ Ort finden und umgek

Forumthread: Bei Eingabe der PLZ Ort finden und umgek

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

PLZ und Ort automatisch in Excel zuweisen

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)

Bei Eingabe der PLZ Ort finden und umgek

Forumthread: Bei Eingabe der PLZ Ort finden und umgek

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

PLZ und Ort automatisch in Excel zuweisen

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)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread