PLZ und ORT Abgleich

[RPP63]

D'accord, Wolfgang!
Ich hatte zwischenzeitlich noch ein wenig herumgeschraubt.
Da gibt es jetzt ein zweites Userform mit dynamischer Listbox des Ortsnamens und einem abhängigem Dropdown der entsprechenden PLZ.
Wer ohne die bisherigen eigenen Versuche, die durchaus gepostet werden dürfen, immer noch Probleme hat, dem wird selbstverständlich geholfen.
Andererseits sehe ich mich als Code-Hure, und das mag ich nicht wirklich …

Aktuelle Version im Anhang.

Gruß Ralf

[snb]

Inzwischen hatte ich mich auch mal an der Arbeit gesetzt. (Hab RPP's Ergebnis noch nicht angeschaut).

[Rabe]

Hi,

die Dateien von RPP63 und snb gefallen mir sehr gut, aber ich verweise noch mal auf meinen Beitrga #7.
Denn der TE will nicht in einer Userform eine PLZ eingeben und dann dort auswählen, sondern die PLZs wie in seiner Beispiel- (#3) und Fenneks Datei (#9) in einer Liste stehen haben und dann in der danebenstehenden Zelle die Orte per Dropdown auswählen.

Es fehlt ihm die Kenntnis, wie er die Auswahl aus einer Userform in ein Arbeitsblatt umbaut, um die Auswahl in einer Zelle/Spalte zu erhalten.

[snb]

@Rabe

Meine Datei enthällt beide Methoden (Userform Und Eingabe ins Arbeitsblatt)

[atilla]

Hallo snb,

Ralf meint etwas anderes.

Ich hoffe, Du bist mir nicht böse, dass ich mich an Deiner Datei vergriffen habe.
Folgendes sollte mit Anpassungen in Deiner Datei, Ralfs Wünsche erfüllen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sn
  If Not Intersect(Target, Range("B3:B10")) Is Nothing Then
    If Target <> "" Then
      sn = Application.Transpose(Sheets("PLZ-Ort").Cells(1).CurrentRegion.Columns(3))
      If 1 + UBound(Filter(sn, Target.Value & ":")) > 0 Then
        If 1 + UBound(Filter(sn, Target.Value & ":")) > 1 Then
          Target.Offset(, 1).Validation.Modify , , , " - mehrere Orte gefunden, bitte auswählen - ;" & Join(Filter(Split(Join(Filter(sn, Target.Value & ":"), "_"), "_"), ":", 0), ";")
          Target.Offset(, 1).Value = Split(Target.Offset(, 1).Validation.Formula1, ";")(0)
        Else
          Target.Offset(, 1).Validation.Modify , , , Join(Filter(Split(Join(Filter(sn, Target.Value & ":"), "_"), "_"), ":", 0), ";")
          Target.Offset(, 1).Value = Split(Target.Offset(, 1).Validation.Formula1, ";")(0)
        End If
      Else
        Target.Offset(, 1).Validation.Modify , , , "keine PLZ"
        Target.Offset(, 1).Value = "keine PLZ"
      End If
    Else
      Target.Offset(, 1).Validation.Modify , , , "Auswahl"
      Target.Offset(, 1).Value = ""
    End If
  End If
End Sub

Ich habe hier den Bereich B3:B10 genutzt. In diesem Bereich sollte eine Gültigkeits Prüfung wie folgt eingerichtet sein:

bei Zulassen: Liste auswählen
bei Quelle: Auswahl eintragen (als Dummy)

Unten die Datei als Kopie:



  Kopie von PLZ-Ort snb.xlsb (Größe: 519,13 KB / Downloads: 4)

[snb]

@Attilla,

Weil die Validation  in B3 nur gültige PLZ-en enthällt ist 'keine PLZ' unmöglich.

Die Bericht "mehrere Werten" scheint mir redundant: wenn die gewünschte Ortsname nicht gezeigt wird, muss sie sich im validation list befinden (weil es nur gültige PLZ's sind).

Weil ich das Suchkonzept mehr robust gemacht habe (keiene ungültige PZL-en), kann die Code sehr beschränkt sein: 'structuring precedes coding'. überprüfungen sind überflüssig.

PS. Deine dim sn steht am falschen Ort: so ist sn nie 'Public', doch 'local'.

[atilla]

@ snb

Weil die Validation  in B3 nur gültige PLZ-en enthällt ist 'keine PLZ' unmöglich.

In B3 ist keine Gültigkeitsliste hinterlegt.


@all
das was ich da verbrochen habe kann man glaube ich in die Tonne werfen.
Da sind einige Dinge, die nicht so funktionieren, wie sie sollten.
Beim Erstellen des Codes, hatte ich Probleme die Gültigkeitsliste mit Kommata aufzubauen, so wie bei snb.
Das habe ich dann auf ";" geändert und es funktionierte.
Nun beim erneuten Öffnen kommt es zum Fehler, gerade deswegen.

Das sind aber alles Dinge, die man noch auf eine Andere Art regeln kann. Man sollte sehen, worauf Ralf(Rabe) hinaus will.

noch einmal @snb
gib bitte bei Dir mal die PLZ 01067 ein und schau was als Liste in C3 erscheint.
Die Filter Funktion scheint hier sehr generös zu filtern.

[snb]

@Att

Ich habe die Generösität etwas eingeschränkt (gut gesehen :18: ).

[atilla]

Hallo zusammen,

zweiter Versuch. Diesmal habe ich Ralfs (RPP63 ) Version verschandelt.


  Kopie von PLZ-Ort per Array (final-Ralf-RPP63).xlsm (Größe: 500,14 KB / Downloads: 0)

Jetzt weiß ich auch warum es beim ersten mal nicht funktioniert hat. Ist eine lange geschichte aber hier von silex1 kurz erklärt: clever-excel-forum.de

Deshalb hier jetzt mit Namensvergabe.


@Ralf(RPP63 )
schau Dir bitte beim Namen OrteGes den Bezug an. Du hast die Formel nicht absolut gesetzt und deswegen werden teils falsche Orte eingelesen.
Gib zum testen mal die PLZ 15913 ein. Der Bereich muss wie bei PLZges ab der ersten Zeile anfangen. Also statt B3 muss B1 und das muss absolut gesetzt werden.


@snb
leider funktioniert Deine letzte Version bei mir gar nicht.

es erscheint ein Fehler mit der Meldung: Laufzeitfehler 1004.  Anwendungs- oder objektdefinierter Fehler.
in dieser Zeile:

Code:

Target.Offset(, 1).Validation.Modify , , , Join(Filter(Split(Join(Filter(sn, ":" & Target.Value & ":"), "_"), "_"), ":", 0), ",")

@snb
Problem behoben, so geht es:

Code:

Target.Offset(, 1).Validation.Modify , , , Join(Filter(Split(Join(Filter(sn, ":" & Format(Target.Value, "00000") & ":"), "_"), "_"), ":", 0), ",")

[RPP63]

Moin!
@Atilla und snb:
Warum macht ihr es euch so schwer?  :21:
Die Anforderung ist völlig ohne VBA machbar.

• intelligente "Tabelle1" mit PLZ und Ort
  
• PLZ Datenüberprüfung, Liste, =PLZges Zellendropdown deaktiviert
  
• Ort Datenüberprüfung, Liste, =Orte_Spezial
  
• Name Orte_Spezial bezieht sich auf:
  
• =INDEX(OrteGes;VERGLEICH(Tabelle1[@PLZ];PLZges;0)):INDEX(OrteGes;VERGLEICH(Tabelle1[@PLZ];PLZges;0)+ZÄHLENWENN(PLZges;Tabelle1[@PLZ])-1)
  

Fertig!
Datei im Anhang.

Gruß Ralf