yotyot
Goto Top

Excel VBA gefilterte Spalten vergleichen

Aloha!

Ich bin seit satten fünf Stunden auf der Suche nach einer funktionierenden Lösung und scheitere daran, dass ich nicht in der Lage bin, fehlerhafte Makros, die ohne Erklärung kommen, zu korrigieren. Nebenbei brennt mir natürlich die Zeit, ist klar... Excel ist eben nicht Access, die Anforderungen ändern sich gerade face-confused

Die Ausgangssituation:
Ich habe eine Excel-Tabelle mit zwei Tabellenblättern, "links" und "rechts". Interessant sind die Spalten links.H und rechts.B. Auf beiden Tabellenblättern befinden sich Datensätze aus unterschiedlichen Quellen, die sich also voneinander unterscheiden, die aber in den genannten Spalten teilweise eine Schnittmenge haben. In links.H können die Werte öfter vorkommen, in rechts.B sind die Werte nur einmal vorhanden. Nun ist die Tabelle "links" auch noch gefiltert, d.h., von gut 1200 Datensätzen werden nach Filterung nur vielleicht 70 angezeigt, auch hier gibt es in links.H doppelte Werte. "rechts" ist nicht gefiltert. Beide Tabellen sind unterschiedlich sortiert.

Die Aufgabe:
Ich will links.H mit rechts.B vergleichen und überall da, wo in rechts.B ein Wert auftaucht, der auch in links.H enthalten ist, den Wert aus rechts.A, also der daneben liegenden Zelle kopieren und in die Zelle(n) links.P einfügen, wo in der entsprechenden Zeile ebenfalls der gefundene Wert steht. Um das ein wenig zu verdeutlichen:

Zeile links.H rechts.A rechts.B
17 45789 b1n2m3 41254
44 45795 j433k43 39877
99 41254 k4l5553 29544
100 41254 l9m666 33998
119 43777 a55f99 45789


In Zeile 99 und 100 steht in links.H also ein Wert, der in rechts.B in Zeile 17 auftaucht. Jetzt soll rechts.A.Zeile17 kopiert werden nach Links.P.Zeilen99 und 100.

Alle Scripts, die ich bisher getestet habe, funktionieren entweder gar nicht oder liefern mir falsche Werte zurück, die nicht stimmen oder schreiben sie in falsche Felder. Sogar die Vorab-Version, dass ich nur die gefundenen Zellen farblich markiere, funktioniert nicht richtig, es werden falsche Zellen eingefärbt, teilweise sogar leere zellen (unterhalb der eigentlichen Tabelle, wenn ich mit der gesamten Spalte arbeite).

Wie macht man sowas? Verschachtelte For each Schleifen, ok, aber irgendwie klappt das zählen nicht richtig...

Kann mir da mal jemand 'nen Zaunpfahl leihen?

Grüße

Y.

Content-Key: 257257

Url: https://administrator.de/contentid/257257

Ausgedruckt am: 28.03.2024 um 14:03 Uhr

Mitglied: colinardo
Lösung colinardo 10.12.2014, aktualisiert am 11.12.2014 um 09:41:13 Uhr
Goto Top
Aloha! Pehea `oe YotYot? face-smile
kein Problem, den Zaunpfahl ramm ich dir doch gleich ganz ins Gehirn face-wink ... es ließe sich zwar auch mit einer einfachen Formel lösen, da du aber so wie es aussieht nach einem Makro suchst, hier die entsprechende Lösung für dein Anliegen:
In Zeile 4 und 6 legst du noch die Namen der Worksheets fest, feddich. Die Zeilen sind alle kommentiert, so dass eine Anpassung nichts im Wege stehen sollte. Ich bin jetzt einfach mal davon ausgegangen das in den Sheets jeweils die erste Zeile eine Überschrift beinhaltet, und die Daten ab Zeile 2 beginnen.

Hier auch noch das Demo-Sheet map_values_257257.xlsm
Sub FindAndSetValues()
    Dim wsLinks As Worksheet, wsRechts As Worksheet, rngSearch As Range, rngRef As Range, cell As Range, firstAddress As String, f As Range
    'linkes Sheet festlegen  
    Set wsLinks = Sheets("links")  
    'rechtes Sheet festlegen  
    Set wsRechts = Sheets("rechts")  
    'Bereich in dem im linken Sheet gesucht wird (nur sichtbare Zellen werden verarbeitet, ausgefilterte nicht)  
    Set rngSearch = wsLinks.Range("H2:H" & wsLinks.UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)  
    'Bereich der Referenz-Werte im rechten Sheet  
    Set rngRef = wsRechts.Range("B2:B" & wsRechts.UsedRange.Rows.Count)  
    
    'Für jede Zelle im Bereich der Referenz-Werte  
    For Each cell In rngRef
        'wenn Zellwert nicht leer ist ...  
        If cell.Value <> "" Then  
            'Suche Wert der Zelle im linken Sheet  
            Set f = rngSearch.Find(cell.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not f Is Nothing Then
                firstAddress = f.Address
                Do
                    'setze Wert im linken Sheet Spalte P auf den Wert von Sheet Rechts Spalte A  
                    f.Offset(0, 8).Value = cell.Offset(0, -1).Value
                    'Alternativ könntest du die obige Zeile auch so schreiben dann ist es für dich vielleicht verständlicher  
                    'wsLinks.Range("P" & f.Row).Value = wsRechts.Range("A" & cell.Row).Value  
                    
                    'Suche ob der Wert erneut vorkommt  
                    Set f = rngSearch.FindNext(f)
                Loop While Not f Is Nothing And f.Address <> firstAddress
            End If
        End If
    Next
End Sub
Grüße Uwe
Mitglied: YotYot
YotYot 11.12.2014 um 09:41:05 Uhr
Goto Top
Man muss halt nur jemanden fragen, der sich auskennt face-wink

Hallo Uwe!

Das war jetzt mal fast unbezahlbar... wenn ich mir überlege, wie viele nicht funktionierende Scripts ich gestestet habe und was ich versucht habe, umzuschreiben mit dem Wissen aus Access und Javascript - aber Excel VBA ist halt doch noch mal 'ne andere Sprache: xlValues, xlWohle usw sind für mich halt noch neu, da muss ich mich noch einfinden.

Läuft. Fehlerfrei. Gleich vom ersten Versuch an. Und außerdem wesentlich schneller, als das, was ich gestern getestet habe. Bin begeistert! Danke!

Und ja, ich wollte es unbedingt in VBA haben, erstens weil ich mich da einarbeiten will, weil es in Zukunft öfter mal Anforderungen in der Richtung geben kann und zweitens sind in den kopierten Zellen IDs drin, die ich zum Erstellen von Weblinks noch weiterverarbeiten muss. Macht dann ja Sinn, wenn alles in einem Guß ist. Außerdem kann man dann bei Bedarf noch ein wenig mehr damit anstellen.

Nochmal: Danke, bin voll begeistert!

Y.

Achso: was heißt eigentlich "Pehea `oe"? Bin soweit gekommen, dass es wohl sowas wie "Was ist", also "Was heißt" vermutlich bedeutet? Yot (Jott) ist der erste Buchstabe meines Namens.

Grüße aus Bremen,

Jörg face-wink
Mitglied: colinardo
colinardo 11.12.2014 um 09:59:51 Uhr
Goto Top
Hallo Joerg,
ich dachte wenn schon auf Hawaiianisch begrüßt muss ich doch Kante zeigen face-smile
Achso: was heißt eigentlich "Pehea `oe"?
>>"Hallo, wie geht's dir ?"<<

Viel Erfolg beim VBAen
Grüße Uwe