Excel VBA - PLZ in Text suchen

jerry0110

Erfahrenes Mitglied
Oder man sortiert die PLZ Liste von Deutschland nach PLZ aufsteigend als Textsortierung (damit 09853 vor 12345 kommt), und macht dann einen binären Suchalgorithmus.
Ich habe hier eine Datenbank im Textformat mit 170K Datensätzen.
Suchzeit: Unter 1 Sekunde pro Suchvorgang auf das sortierte Kriterium

Die PLZ Liste ist sortiert.
 

Zvoni

Erfahrenes Mitglied
In einer eigenen Spalte?
Ist nicht wirklich wichtig solange ein Delimiter drin ist.
Deine initiale Schwierigkeit ist eher, die PLZ aus deinem Datensatz zu extrahieren.
Wenn der Aufbau immer gleich ist (ein Komma nach der Strasse und vor PLZ und Ort) würde ich nen doppelten Split machen.
Erster Split auf Komma, du erhälst ein Array mit Elementen 0 und 1
Dann ein Trim auf Element 1 (um eventuelle Leerzeichen vorne zu entfernen),
Danach der zweite Split auf Leerzeichen.
In diesem Ergebnis enthält Element 0 die PLZ
 

Yaslaw

n/a
Moderator
Oder ein RegEx und gleich die ganze Zeile normaliseren (also in verschiedene Spalten aufteilen).

Du kannst aber auch mittels RegEx deine Suche definieren. Das hilft, wenn die Adresse nicht immer gleich aufgebaut ist
Visual Basic:
Dim rx As Object
Dim plz As String
Set rx = CreateObject("VBScript.RegExp")

...
        plz = wsPlz.Range("C" & rowNrPlz).Value    'Die Postleitzahl aus der Postleitzahltabelle
        rx.pattern = "\b" & plz "\b"
        if rx.test(wsProj.Range("F" / rowNrProj).Value) Then
            ---
 

jerry0110

Erfahrenes Mitglied
Es funktioniert perfekt

Code:
Sub test()

Dim source As Worksheet
Dim ziel As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim f As Long
Dim i As Long
Dim Suche As String
Dim rx As Object
Dim plz As String
Set rx = CreateObject("VBScript.RegExp")

Set source = ActiveWorkbook.Sheets("PLZ")
Set ziel = ActiveWorkbook.Sheets("Sheet1")

For f = lastRowNr(source) To 2 Step -1
        
        For i = lastRowNr(ziel) To 2 Step -1
                
                plz = source.Range("C" & f).Value
                rx.Pattern = "\b" & plz & "\b"
                    
                    If rx.test(ziel.Cells(i, 6).Value) Then
                        ziel.Range("P" & i).Value = source.Range("E" & f).Value
                        ziel.Range("Q" & i).Value = source.Range("D" & f).Value
                        ziel.Range("R" & i).Value = source.Range("B" & f).Value
                    End If
                    
        Next i
Next f
    

End Sub
 

Yaslaw

n/a
Moderator
Noch als Nachtrag zum RegEx
Dein Pattern sieht zusammengesetzt etwa so aus: "\b48739\b"
\b steht für Wortbruch. Also es beginnt oder endet ein Wort. Somit findet er die PLZ wenn links oder rechts davon etwa der folgende Liste vorkommt
Leerzeichen, Satzzeichen, Bindestrich, Zeilenumbruch, Textende oder Textanfang.
Es wird nicht funktionieren, wenn direkt nach der Plz noch ein Buchstabe oder so hängt.