Excel VBA - PLZ in Text suchen


jerry0110

Erfahrenes Mitglied
#1
Hallo zusammen,

ich habe folgenden Ausgangspunkt. Eine Datei mit 2 Sheets. Eine mit Daten von Projekten. In Spalte F ist eine Straße mit PLZ und Ort.
Im 2ten Sheet ist eine komplette PLZ Liste aus Deutschland. Ich möchte jetzt die PLZ Liste mit einer for schleife durchgehen und dann die PLZ die dann als erstes gefunden wird im Text aus Spalte F im ersten Sheet suchen.

Wie bekomme ich die Suche hin.
Das andere sind ja nur 2 Schleifen.
 

Yaslaw

n/a
Moderator
#2
Was hast du schon versucht?

Aber wozu ein VBA-Code? Ein VLookup im Excel tuts doch auch
Code:
=VLOOKUP($F3;PLZ!$A:$B;2;FALSE)
In Deutsch heisst der Befehl glaub anders...
 

jerry0110

Erfahrenes Mitglied
#3
Was hast du schon versucht?

Aber wozu ein VBA-Code? Ein VLookup im Excel tuts doch auch
Code:
=VLOOKUP($F3;PLZ!$A:$B;2;FALSE)
In Deutsch heisst der Befehl glaub anders...
VLookup ist Sverweis in Deutsch. Aber da kann ich nur eindeutige Dinge suchen.
Wenn ich z. B. statt false, true nehme dann sucht er eine ungefähre Übereinstimmung.
Aber er sucht nicht nach der PLZ direkt.
 

Yaslaw

n/a
Moderator
#4
Und? Bekommst du damit falsche Resultate?

ZU VBA: Du weisst schon, dass die Schleifen über Zellen eher eine langsame Sache sind.
Falls es unbedingt VBA sein soll, was hast du schon versucht?
 

jerry0110

Erfahrenes Mitglied
#5
Ich habe das hier schon gemacht. Nur jetzt muss ich halt die Suche anpassen.
Quasi bei der If schleife muss ich ja sagen, suche in dem Text nachdem was in Spalte C ist.

Visual Basic:
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

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
        If source.Range("C" & f).Value = ziel.Range("F" & i).Value Then
            'mach das und das
        End If
    Next i
Next f
    

End Sub
 

Yaslaw

n/a
Moderator
#6
Aber das hast du doch schon.
Visual Basic:
If source.Range("C" & f).Value = ziel.Range("F" & i).Value Then
Was geht daran nicht?

Grundsätzlich würde ich die Logik noch umdrehen. Du suchst jetzt zu jeder PLZ die passenden Projekte.
zudem wenn man die Variabeln sinnvoll benennt, dann sieht man ziemlich klar, was wie zusammengehört
Visual Basic:
Dim wsPlz         As Worksheet
Dim wsProj         As Worksheet
Dim rowNrPlz     As Long
Dim rowNrProj     As Long

For rowNrProj = lastRowNr(wsProj) To 0 Step = -1
    For rowNrPlz = lastRowNr(wsPlz) To 0 Step = -1
        If wsProj("C" & rowNrProj).Value = wsPlz("F" & rowNrPlz) Then
            wsProj("D" & rowNrProj).Value  = wsPlz("G" & rowNrPlz)    'Wert übernehmen
            Exit For                                                 'Innere Schleife verlassen und nächstes Projekt bearbeiten
        End If
    Next    
Next
 

jerry0110

Erfahrenes Mitglied
#7
Aber das hast du doch schon.
Visual Basic:
If source.Range("C" & f).Value = ziel.Range("F" & i).Value Then
Was geht daran nicht?

Grundsätzlich würde ich die Logik noch umdrehen. Du suchst jetzt zu jeder PLZ die passenden Projekte.
zudem wenn man die Variabeln sinnvoll benennt, dann sieht man ziemlich klar, was wie zusammengehört
Visual Basic:
Dim wsPlz         As Worksheet
Dim wsProj         As Worksheet
Dim rowNrPlz     As Long
Dim rowNrProj     As Long

For rowNrProj = lastRowNr(wsProj) To 0 Step = -1
    For rowNrPlz = lastRowNr(wsPlz) To 0 Step = -1
        If wsProj("C" & rowNrProj).Value = wsPlz("F" & rowNrPlz) Then
            wsProj("D" & rowNrProj).Value  = wsPlz("G" & rowNrPlz)    'Wert übernehmen
            Exit For                                                 'Innere Schleife verlassen und nächstes Projekt bearbeiten
        End If
    Next   
Next

im ersten Teil klappt es, wenn in z. B. F2 nur die PLZ stehen würde. Dann würde er ja ein Match haben.
Aber in Zelle F2 steht dann ja Straße, PLZ und Ort und vielleicht noch ein paar Dinge.
 

jerry0110

Erfahrenes Mitglied
#9
Heeker Str., 48739 Legden-Asbeck

Ich hab jetzt mal geguckt und ich könnte doch das nehmen, oder?

Visual Basic:
If InStrRev(source.Range("F" & i).Value, Suche) Then
 

Zvoni

Erfahrenes Mitglied
#10
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
 

jerry0110

Erfahrenes Mitglied
#11
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
#12
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
#13
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
#14
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
#15
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.