Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1856to1860
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Daten extern suchen/ersetzen

Daten extern suchen/ersetzen
02.12.2021 22:04:11
Benedikt
Hallo,
ich habe hier ein Programm welches mir immer Stücklisten ausgibt, leider kann ich die so nicht weiterverarbeiten.
Ich muss den Teilenamen in Spalte A von der Stückliste immer durch eine Nummer ersetzen.
Jetzt dachte ich mir, ich erstelle eine Datenbank Tabelle in der in Spalte A die Nummer und in Spalte B der Teilenamen steht.
Dann soll das Makro von der Stückliste aus den abgleich in der Datenbank machen und Zellen immer ersetzen.
Nun versuche ich einen Weg zu finden damit das "automatisch" passiert.
Was ich rausgefunden habe ist, dass man die Makros in einer persöhnlichen Arbeitsmappe speichern kann und die somit in allen zur verfügung stehen.
Außerdem habe ich gelesen, dass man mit VBA Daten aus einer anderen Datei holen/abgleich kann, dazu wird die Datei dann irgendwie automatisch im hintergrund geöffnet.
Weiter bin ich leider noch nicht und hoffe hier kann mir jemand evtl. ein wenig helfen^^
Ich habe im Anhang mal ein kleiner Beispiel erstellt.
https://www.herber.de/bbs/user/149543.zip
Danke und Gruß

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten extern suchen/ersetzen
03.12.2021 09:59:19
UweD
Hallo
- pack das hier in ein Modul in der Personal.xlsb
- Pfade etc. anpassen
- dann aus der geöffneten Datei heraus aufrufen
- Die Datenbank muss nicht geöffnet sein

Sub Tauschen()
Dim TB As Worksheet, Pfad As String, Datei As String
Dim EW As String, LR As Long, LC As Integer
Pfad = "E:\Excel\Temp\Test\" ' inkl. \ am Ende
Datei = "Datenbank(1).xlsx"
EW = "?" 'Ersatzwert
Set TB = ActiveWorkbook.ActiveSheet
LR = TB.Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
LC = TB.Cells.SpecialCells(xlCellTypeLastCell).Column + 1 'Letzte Spalte des gesamten Blattes +1
With TB.Cells(1, LC).Resize(LR, 1)
.FormulaR1C1 = "=XLOOKUP(RC1,'" & _
Pfad & "[" & Datei & "]Tabelle1'!C2,'" & _
Pfad & "[" & Datei & "]Tabelle1'!C1,""" & EW & """,0)"
TB.Cells(1, 1).Resize(LR, 1).Value = .Value
End With
TB.Columns(LC).Delete
End Sub
ich benutze benutze temporär eine Hilfspalte mit einer XVerweis- Formel
Die Werte werden dann in Spalte A eingefügt und die Hilfsspalte wieder gelöscht
LG UweD
Anzeige
AW: Daten extern suchen/ersetzen
03.12.2021 19:33:44
Benedikt
Hallo,
vielen Dank. Das funktioniert so weit.
Jetzt müsste ich nur noch wissen was bei EW rein muss, dass der einfach den Zelleninhalt behält wenn er nichts findet^^
Vielen dank
AW: Daten extern suchen/ersetzen
04.12.2021 21:27:13
Benedikt
Hallo,
vielen Dank. Das funktioniert so weit.
Jetzt müsste ich nur noch wissen was bei EW rein muss, dass der einfach den Zelleninhalt behält wenn er nichts findet^^
Vielen dank
AW: Daten extern suchen/ersetzen
03.12.2021 10:15:27
Yal
Hallo Benedikt,
Im Prinzip ist es nur einem SVerweis, mit der Besonderheit, dass das Ergebnis die originale Werte überschreibt.
Ich würde temporär eine Spalte vor der Spalte A einfügen, den Sverweis einbauen, das Ergebnis kopieren und als Wert auf Spalte B ablegen, dann die Spalte A wieder löschen.
Wenn Du das unter laufende Makro Rekorder machst, hast Du den Code den Du brauchst. Für die Feinheiten, wie Code in einem separaten Datei, fragt am besten wenn Du soweit bist.
VG
Yal
Anzeige
AW: Daten extern suchen/ersetzen
04.12.2021 18:21:50
Benedikt
Also den SVERWEIS habe ich hinbekommen.
Nur weiß ich nicht wie ich das so kopieren kann, dass er, wenn der nichts findet, den Zelleninhalt behält.
Das gleiche Problem habe mich mit dem Makro von Uwe.
AW: Daten extern suchen/ersetzen
04.12.2021 19:20:50
Yal
Hallo Benedikt,
Du hast den Haken bei "Fragen noch offen" nicht gelegt, sodass ich deinen Nachtrag fast übersehen hätte.
(Basierend auf dem Code von Uwe)

Sub Tauschen()
Dim Z As Range
Dim Erg As String
Const Pfad = "E:\Excel\Temp\Test\" ' inkl. \ am Ende
Const Datei = "Datenbank(1).xlsx"
Const Bereich = "A:B"
With ActiveWorkbook.ActiveSheet
For Each Z In .Range(.Range("A1"), .Range("A99999").End(xlUp)).Cells
Erg = ""
Erg = Application.Evaluate("=XLOOKUP(" & Z.Value & ",'" & _
Pfad & "[" & Datei & "]Tabelle1'!" & Bereich & ",2,0)")
If Not IsError(Erg) Then Z = Erg
Next
End With
End Sub
Ungetestet.
Eventuell gibt es Nacharbeit-Bedarf auf dem If not IsError() da es sich hier um eine VBA-Fehlerprüfung und nicht unebdingt um eine Daten-Fehlerprüfung.
Notfalls

If Not WorksheetFunction.IsError(Erg) Then Z = Erg
VG
Yal
Anzeige
AW: Daten extern suchen/ersetzen
04.12.2021 20:25:24
Benedikt
Hi,
den hatte ich garnicht gesehen ^^ Sorry.
Ich bekomme immer einen Laufzeitfehler bei:
Erg = Application.Evaluate("=XLOOKUP(" & Z.Value & ",'" & _
Pfad & "[" & Datei & "]Tabelle1'!" & Bereich & ",2,0)")
AW: Daten extern suchen/ersetzen
04.12.2021 21:12:42
Yal
Hallo Benedikt,
Am bestens herstellst Du diese Formel in deinem Datei unter laufenden Makro Rekorder und poste hier den Code. Dann kann ich sehen, wie es zu korrigieren ist.
VG
Yal
AW: Daten extern suchen/ersetzen
04.12.2021 21:26:36
Benedikt
Hallo Yal,
ich kann dir leider nicht ganz folgen.
Ich habe deinen Code in ein Makro kopiert und dann beim ausführen die Fehlermeldung erhalten.
https://www.herber.de/bbs/user/149586.zip
Anzeige
AW: Daten extern suchen/ersetzen
05.12.2021 00:07:02
Yal
Hallo Benedikt,
ok, kleine Unstimmigkeiten. Das Evaluate funktioniert doch nicht wie erwartet.
Die Schwierigkeit ist, dass wir möchten, dass es auch funktioniert, wenn die Datei "Datenbank" geschlossen ist. Und das geht nur, wenn es über eine Formel geht, bei der den gesamten Pfad drin steht.
Also Formel in eine Zelle einfügen, Wert abholen, wenn gültig Spalte A überschreiben, dann Formel wieder löschen.
Es geht so:

Sub Tauschen()
Dim Z As Range
Dim Temp As Range
Const Pfad = "C:\Users\Bene\Desktop\149543\" ' inkl. \ am Ende
Const Datei = "Datenbank(1).xlsx"
With ActiveWorkbook.ActiveSheet
For Each Z In .Range(.Range("A1"), .Range("A99999").End(xlUp)).Cells
Set Temp = .Range("XFA").End(xlToLeft).Offset(0, 1) 'erste leere Zelle in der Zeile
Temp.FormulaR1C1 = "=VLookup(RC1,'" & Pfad & "[" & Datei & "]Tabelle1'!C1:C2, 2, 0)"
If Not IsError(Temp) Then Z = Temp.Value
Temp.ClearContents
Next
End With
End Sub
Ein wichtiger Punkt noch: wenn Du möchtest, dass der SVerweis aus "Banane" einen Zahl macht, dann muss "Banane" in Spalte A und Der zahl in Spalte B von "Datenbank" sein. Also beide Spalten vertauschen.
VG
Yal
Anzeige
AW: Daten extern suchen/ersetzen
05.12.2021 09:42:05
Benedikt
Hallo,
danke. Leider gibt der mir immer noch einen Laufzeitfehler 1004 in folgender Zeile aus:
Set Temp = .Range("XFA").End(xlToLeft).Offset(0, 1)
Ich habe mal versucht das selber zu verstehen:
Ich glaube da wird eine temporäre neue Spalte links neben der Spalte A erstellt und dann wieder die "alte Spalte A" ausgewählt.
was dieses XFA bei Range ist weiß ich leider nicht.
Den Fehler finde ich leider auch nicht.
https://www.herber.de/bbs/user/149591.zip
AW: Daten extern suchen/ersetzen
05.12.2021 09:42:45
Benedikt
Hallo,
danke. Leider gibt der mir immer noch einen Laufzeitfehler 1004 in folgender Zeile aus:
Set Temp = .Range("XFA").End(xlToLeft).Offset(0, 1)
Ich habe mal versucht das selber zu verstehen:
Ich glaube da wird eine temporäre neue Spalte links neben der Spalte A erstellt und dann wieder die "alte Spalte A" ausgewählt.
was dieses XFA bei Range ist weiß ich leider nicht.
Den Fehler finde ich leider auch nicht.
https://www.herber.de/bbs/user/149591.zip
Anzeige
AW: Daten extern suchen/ersetzen
05.12.2021 10:28:11
Yal
Hallo Benedikt,
Asche über meinen Haupt: XFA1
XFA ist die Spaltename. Eigentlich könnte man genauso ZZ nehmen, den so viele belegten Spalten hast Du sicher nicht.
Range erwartet aber nicht eine Spalte sondern eine Zelle also XFA1 oder ZZ1.
Was passiert dabei? Man nimmt eine Zelle ganz weit links (ZZ1) und sucht mit End(xlToLeft) die erstbefüllte Zelle in der Zeile in dieser Richtung. Mit Offset(Zeile, Spalte) verschiebt man den Auswahl um eine Zelle nach Recht, also die erste Zelle rechts von der letzten befüllte Zelle in der Zeile.
VG
Yal
AW: Daten extern suchen/ersetzen
05.12.2021 10:38:42
Benedikt
Ok danke.
Jetzt ist nur noch das Problem da, dass er nur Zelle A1 ändert und das nicht auf die ganze Spalte anwendet.
Anzeige
AW: Daten extern suchen/ersetzen
05.12.2021 11:32:47
Yal
Mist. Ich bin nur am Handy und kann den Code nicht testen. Daher übersehe ich manches.

Set Temp = .Range("XFA" & Z.Row).End(xlToLeft).Offset(0, 1)
Mit der Zelle ("Z") und daraus die Zeilennummer Z.Row ist in jeder Iteration der Schleife den richtigen Nummer.
Ich hoffe, es passt jetzt. Schwere Geburt.
VG
Yal
AW: Daten extern suchen/ersetzen
05.12.2021 12:06:29
Benedikt
Hallo, vielen dank es funktioniert.
Vielen Dank und Gruß
AW: Daten extern suchen/ersetzen
05.12.2021 12:49:12
Benedikt
Nur für mein Verständniss und falls das noch einer liest:

Sub Tauschen() ' Name
Dim Z As Range ' Zelle wird als Range definiert
Dim Temp As Range ' Temp wird als Range definiert (Temp=temporär)
Const Pfad = "C:\Users\Bene\Desktop\149543\" 'Pfad zur Datenbank
Const Datei = "Datenbank(1).xlsx" ' Datenbank name
With ActiveWorkbook.ActiveSheet ' mit aktiver Tabelle ausführen
For Each Z In .Range(.Range("A1"), .Range("A99999").End(xlUp)).Cells ' Bereich der durchsucht wird, was ich nicht verstehe warum das dann nicht klappt wenn ich alles in z.B. Spalte B kopiere und hier B1 usw. eintrage
Set Temp = .Range("XFA" & Z.Row).End(xlToLeft).Offset(0, 1) ' Temporäre Zelle neben der letzten beschriebenen Zelle in Spaltenrichtung
Temp.FormulaR1C1 = "=VLookup(RC1,'" & Pfad & "[" & Datei & "]Tabelle1'!C1:C2, 2, 0)" ' Das wird die suche in der Datenbank sein, verstehe ich aber nicht so recht
If Not IsError(Temp) Then Z = Temp.Value ' Das ist quasie " Wenn Error dann Zelle behalten
Temp.ClearContents ' das löscht alles temporäres
Next ' nächste Zelle in Zeilenrichtung und wieder alles von vorne
End With
End Sub

Anzeige

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige