Zwei Tabellen vergleichen Änderung eintragen

ElaD · 9. November 2019 um 11:11

Hallo nochmal,

da mein eines Problem ja schon so schön schnell gelöst wurde (Danke nochmal ), habe ich gleich das nächste:

In dem vorherigen Problem habe ich ja nun eine Tabelle, nennen wir sie Tab1 nach folgendem Schema:
Spalte A = Pfad
Spalte B = Datei

Nun habe ich in einer anderen Tabelle (Tab2) folgenden Aufbau:
Spalte C = Dateiname (gleicher Name wie in Tab1)
Spalte F = Neuer Dateiname

Ich möchte nun meine Tab1 um die Spalte D erweitern und zwar mit dem neuen Dateinamen.
Tab1.SpalteD = neuer Dateiname

Dazu müsste man ja sinngemäß:

Schaue in Tab1.SpalteB 'Einlesen des Strings
Suche in Tab2.SpalteC = Tab1.SpalteB 'finde eine Übereinstimmung
Wenn gefunden, dann nehme aus gleicher Zeile Tab2.SpalteE und setze Tab1.SpalteD = Tab2.SpalteE
Und das als Schleife für alle Dateinamen die in Tab1 gespeichert sind.
Wenn Du eine Datei nicht findest, dann makiere Tab1.SpalteD rot und schreibe „Datei nicht gefunden“

So in etwa wäre das was ich will in verbal. Sorry aber um das in VB zu schreiben, bin ich etwas zu blöd.

Hoffe mir kann jemand helfen

Reinhard_fb8cb7 · 9. November 2019 um 11:13

Ich möchte nun meine Tab1 um die Spalte D erweitern und zwar
mit dem neuen Dateinamen.
Tab1.SpalteD = neuer Dateiname

Hi Michael,
bin der meinung folgender Code müßte funktionieren:

Option Explicit
Sub tt()
Dim ws1 As Worksheet, ws2 As Worksheet, zei1 As Long, zei2 As Long
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
zei2 = ws2.Range("A65536").End(xlUp).Row
With ws1
 For zei1 = 2 To .Range("A65536").End(xlUp).Row
 If Application.WorksheetFunction.CountIf(ws2.Range("A1:A" & zei2), .Cells(zei1, 2)) \> 0 Then
 .Cells(zei1, 4) = Application.WorksheetFunction.VLookup(ws2.Range("A2:B" & zei2), .Cells(zei1, 2), 2, 0)
 Else
 .Cells(zei1, 4).Font.ColorIndex = 3
 .Cells(zei1, 4) = "nicht gefunden"
 End If
 Next zei1
End With
End Sub

Leider sieht Excel das anders und macht aus:

Tabellenblattname: Tabelle2
 A B 
1 datei neudatei 
2 dat1 dat111 
3 dat2 dat222 
4 dat4 



Tabellenblattname: Tabelle1
 A B C D
1 pfad datei neudatei 
2 C:\test dat1 #Bezug!
3 c:\temp dat2 #NV!
4 C:\temp dat3 nicht gefunden

Viellecht sieht jmd anders den Fehler, ich derzeit nicht.
Gruß
Reinhard

Reinhard_fb8cb7 · 9. November 2019 um 11:13

so gehts
Hi,

Option Explicit
Sub tt()
Dim ws1 As Worksheet, ws2 As Worksheet, zei1 As Long, zei2 As Long
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")
zei2 = ws2.Range("A65536").End(xlUp).Row
With ws1
 For zei1 = 2 To .Range("A65536").End(xlUp).Row
 If Application.WorksheetFunction.CountIf(ws2.Range("A1:A" & zei2), .Cells(zei1, 2)) \> 0 Then
 .Cells(zei1, 4) = Application.WorksheetFunction.VLookup(.Cells(zei1, 2), ws2.Range("A2:B" & zei2), 2, 0)
 Else
 .Cells(zei1, 4).Font.ColorIndex = 3
 .Cells(zei1, 4) = "nicht gefunden"
 End If
 Next zei1
End With
End Sub

Gruß
Reinhard

ElaD · 9. November 2019 um 11:18

Hallo Reinhard,

wie immer erstmal ein großes Danke schön.
Du hast mir sehr viel weitergeholfen.

aber das nächste Problem steht schon wieder an und ich verstehe es nicht.

Nachdem dein Quelltext so gut funktioniert hat. Habe ich Ihn noch für ein anderes Beispiel benutzt und angepasst.

Sachverhalt:
Tabelle 1
A = alte Pfadstruktur
B = alte Dateiname
C = neue Pfadstruktur
D = neue dateiname

Tabelle 2
A = neue Pfadstruktur
B = neuer dateiname

Die Tabelle 1 ist vollständig ausgefüllt.
Nun möchte ich anhand der neuen Struktur sehen, wo denn meine alten dateien nun liegen.

Deshalb müsste das ja so aussehen
Tabelle 2.Spalte C = Tabelle 1.Spalte A 'denn da soll der alte Pfad rein
Tabelle 2.Spalte D = Tabelle 1.Spalte B 'und hier der alte Name

Folgendes habe ich aus Deinem Quellcode gebastelt:
Sub Mapping_Tab1_Tab2_Test()

Dim ws1 As Worksheet, ws2 As Worksheet, zei1 As Long, zei2 As Long

Set ws1 = Worksheets(„Tabelle2“) 'Tabelle 2
Set ws2 = Worksheets(„Tabelle1“) 'Tabelle 1

zei2 = ws2.Range(„A65536“).End(xlUp).Row 'zei2 = wie viele Zeilen in Tabelle 1

With ws1

For zei1 = 2 To .Range(„A65536“).End(xlUp).Row ’ zei1 = wie viele Zeilen in Tabelle 2

If Application.WorksheetFunction.CountIf(ws2.Range(„D2:smiley:“ & zei2), .Cells(zei1, 2)) > 0 Then 'ws2.Range(„d2:d“ & Zei2) = Suche in Tabelle 1 Spalte D nach .Cells(zei1, 2) = Tabelle 2 Spalte B
.Cells(zei1, 3) = Application.WorksheetFunction.VLookup(.Cells(zei1, 2), ws2.Range(„a2:d“ & zei2), 1, 1)
.Cells(zei1, 4) = Application.WorksheetFunction.VLookup(.Cells(zei1, 2), ws2.Range(„a2:d“ & zei2), 2, 1)
Else
.Cells(zei1, 4).Font.ColorIndex = 3
.Cells(zei1, 4) = „nicht gefunden“
End If

If .Cells(zei1, 3) = 0 Then
.Cells(zei1, 3).Font.ColorIndex = 3
.Cells(zei1, 3) = „nicht gefunden“
End If

Next zei1

End With

End Sub

Das ganze Problem ist jetzt, dass er aus der Tabelle 1 immer den letzten Eintrag nimmt und diesen komplett in Tabelle 2 einträgt.
Das verstehe ich nicht. Wieso landet er immer bei dem letzten Eintrag in Tabelle 1, wo doch zum Beispiel der erste Eintrag aus Tabelle 2 in Tabelle 1 auf Platz 10 steht???

Viele Grüße
ElaD

Reinhard_fb8cb7 · 9. November 2019 um 11:18

Hi Michael,
korrigiere mal ws1 zu Tabelle1 und ws2 zu tabelle2
In der With ws1-Schleife edeutet:
Cells() dass man sich auf das aktive Blatt bezieht
.Cells() dass man sich auf das hinter With, hier also ws1 bezieht.
Gruß
Reinhard

Sub Mapping_Tab1_Tab2_Test()

Dim ws1 As Worksheet, ws2 As Worksheet, zei1 As Long, zei2 As
Long

Set ws1 = Worksheets(„Tabelle2“) 'Tabelle 2
Set ws2 = Worksheets(„Tabelle1“) 'Tabelle 1

zei2 = ws2.Range(„A65536“).End(xlUp).Row 'zei2 = wie viele
Zeilen in Tabelle 1

With ws1

For zei1 = 2 To .Range(„A65536“).End(xlUp).Row ’ zei1 =
wie viele Zeilen in Tabelle 2

If
Application.WorksheetFunction.CountIf(ws2.Range(„D2:smiley:“ &
zei2), .Cells(zei1, 2)) > 0 Then 'ws2.Range(„d2:d“ & Zei2)
= Suche in Tabelle 1 Spalte D nach .Cells(zei1, 2) = Tabelle 2
Spalte B
.Cells(zei1, 3) =
Application.WorksheetFunction.VLookup(.Cells(zei1, 2),
ws2.Range(„a2:d“ & zei2), 1, 1)
.Cells(zei1, 4) =
Application.WorksheetFunction.VLookup(.Cells(zei1, 2),
ws2.Range(„a2:d“ & zei2), 2, 1)
Else
.Cells(zei1, 4).Font.ColorIndex = 3
.Cells(zei1, 4) = „nicht gefunden“
End If

If .Cells(zei1, 3) = 0 Then
.Cells(zei1, 3).Font.ColorIndex = 3
.Cells(zei1, 3) = „nicht gefunden“
End If

Next zei1

End With

End Sub

Das ganze Problem ist jetzt, dass er aus der Tabelle 1 immer
den letzten Eintrag nimmt und diesen komplett in Tabelle 2
einträgt.
Das verstehe ich nicht. Wieso landet er immer bei dem letzten
Eintrag in Tabelle 1, wo doch zum Beispiel der erste Eintrag
aus Tabelle 2 in Tabelle 1 auf Platz 10 steht???

Viele Grüße
ElaD

ElaD · 9. November 2019 um 11:20

Hallo Reinhard,

das ist ja das Problem.
Er soll mir das Ergebnis ja in die Tabelle 2 schreiben. Und anhande der in Tabelle 2 eingetragenen Werte den Vergleich aus Tabelle 1 ziehen. Also müsste doch rein theoretisch die Tabelle 2 = ws 1 sein, oder?

Nächstes Problem ist er lässt mir in der Funktion VLookUp als Sortierindex die 0 nicht zu, da kommt immer die Fehlermeldung: Laufzeitfehler 1004 - Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.

Mit der 1 funktioniert es. Das Problem, die 1 bedeutet ja das die Daten sortiert vorliegen. Tun Sie aber nicht, deshalb bräuchte ich da ja eine 0. Warum lässt er mir diese nicht zu. Bei den anderen Verweisen war das nie ein Problem und auf einmal kommt der mit dieser Fehlermeldung!!! Ich versteh die Welt nicht mehr…

Viele Grüße
ElaD

Hi Michael,
korrigiere mal ws1 zu Tabelle1 und ws2 zu tabelle2
In der With ws1-Schleife edeutet:
Cells() dass man sich auf das aktive Blatt bezieht
.Cells() dass man sich auf das hinter With, hier also ws1
bezieht.
Gruß
Reinhard

Reinhard_fb8cb7 · 9. November 2019 um 11:27

Hi Michaela,
habe eben bis 22 Uhr gearbeitet, dann noch paar Abschaltbierchen im Lokal, war/bin deshalb zu faul den Code nochmal anzuschauen, deshalb Antworten frei ausm Bauch.

das ist ja das Problem.
Er soll mir das Ergebnis ja in die Tabelle 2 schreiben. Und
anhande der in Tabelle 2 eingetragenen Werte den Vergleich aus
Tabelle 1 ziehen. Also müsste doch rein theoretisch die
Tabelle 2 = ws 1 sein, oder?

Never.
Set ws1=worksheets(„Tabelle2“)
Set ws2=worksheets(„Tabelle1“)
lä0t Vba zwar zu, warunm auch nicht, ist aber nur dazu dienlich deinen Code absolut grundlos verwirrend zu machen.
Set ws1=worksheets(„Tabelle1“)
Set ws2=worksheets(„Tabelle2“)
führt zu keinen Verwirrungen, mache das bitte.

Nächstes Problem ist er lässt mir in der Funktion VLookUp als
Sortierindex die 0 nicht zu, da kommt immer die Fehlermeldung:
Laufzeitfehler 1004 - Die VLookup-Eigenschaft des
WorksheetFunction-Objektes kann nicht zugeordnet werden.

Das heisst, der gesuchte Begriff wurde nicht gefunden. Auch wenn du ihn in der Liste siehst und er genausoso aussieht wie der Suchbegriff, vba sieht den Begriff anders wie du, es vergleicht nicht den angezeigten Text miteinander sondern den tatsächlich in den Zelle stehenden Text, und da können sie variieren auch wenn sie für dich gleich aussehen.

Mit der 1 funktioniert es. Das Problem, die 1 bedeutet ja das
die Daten sortiert vorliegen. Tun Sie aber nicht, deshalb
bräuchte ich da ja eine 0. Warum lässt er mir diese nicht zu.
Bei den anderen Verweisen war das nie ein Problem und auf
einmal kommt der mit dieser Fehlermeldung!?!?! Ich versteh die
Welt nicht mehr…

egal ob 0 oder 1, der Begriff wurde nicht gefunden. Bei 1 kommt halt keine Fehlermeldung und es wird irgendein Wert zurückgeliefert, egal ob Liste sortiert ist oder nicht. Sortierung bedeutet nur „welcher“ Wert zurückgeliefert wird. Da ich 1 nie benutze habe ich da keine Erfahrungswerte wie die Regularien da sind.
Wenn die Liste sortiert ist bekommste halt den nächstkleineren Wert, wenn unsortiert, irgendeinen Wert *annehm*

Viele Grüße
ElaD

dito
Reinhard