Über VBA einen SVERWEIS mit variabler Bezugs-Datei

Forumthread: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

05.09.2019 16:51:15

Jasper

Hallo zusammen,
folgende Situation:
Ich habe zwei Dateien - ein Export und eine Datei voller Speditionsinformationen.
Beide Dateien werden jede Woche neu geschickt bzw exportiert.
Ich muss aus der Datei "Export" einen SVERWEIS eintragen, der sich auf die Datei der Speditionsfirma bezieht. Der Sverweis prüft drei Tabellenblätter ab. Falls der Wert etwas findet, erscheint "Treffer", sonst "Kein Treffer". Die Formel sind in ganz normaler Formel so aus (I know, wäre auch mit ner Und-Formel schöner aussehen können):

=WENN(ISTNV(SVERWEIS(A2;'[Speditiondat.xlsm]Spedition1'!$A:$B;2;0))=WAHR;
WENN(ISTNV(SVERWEIS(A2; '[Speditiondat.xlsm]Spedition2'!$A:$B;2;0))=WAHR;
WENN(ISTNV(SVERWEIS(A2; '[Speditiondat.xlsm]Spedition3'!$A:$B;2;0))=WAHR;
"Kein Treffer";"Treffer");"Treffer);"Treffer")

So weit so gut, quick and dirty. Ich möchte das aber alles automatisieren und ein "Tool" mit einem Makro bauen, wo ich die beiden Excel-Dateien als Variable anspreche. Weiter unten im Makro füge ich mit dem Makro einen SVERWEIS ein und hier ist mein Problem:
Wie bekomme ich den SVERWEIS im Makro so hin, dass die angesprochene Datei im SVERWEIS variabel ist?
Das ist mein Code soweit mit starrem SVERWEIS, der auch soweit funktioniert (wird noch einiges dran getan, einige Logiken einfach ignorieren):

Option Explicit
Sub Zolldaten_aktualisieren()
Dim Anzahl_Aufträge As Integer
Dim Anzahl_Aufträge_NB As Integer
Dim Export As Variant
Dim DHL_Schenker_Kuehne As Variant
Dim MTO As Variant
MsgBox "Bitte wähle deine Zieldatei/ den Export", vbInformation
Export = Application.GetOpenFilename()
MsgBox "Bitte wähle die Datei, welche die Information von Speditionen enthält", vbInformation
DHL_Schenker_Kuehne = Application.GetOpenFilename()
Application.ScreenUpdating = False
Application.EnableEvents = False
Workbooks.Open (Export)
With ActiveWorkbook.Sheets(1)
.Range("$A$1:$F$5000").RemoveDuplicates Columns:=1, Header:=xlYes
Anzahl_Aufträge = WorksheetFunction.CountA(.Range("A1:A5000"))
.Range(.Cells(2, "L"), .Cells(Anzahl_Aufträge, "L")).FormulaR1C1 = "=MID(RC[-9],9,3)"
.Range(.Cells(2, "G"), .Cells(Anzahl_Aufträge, "G")).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-6],'[Speditiondat.xlsm]Spedition1'!C1:C2,2,0))=TRUE,_
IF(ISNA(VLOOKUP(RC[-6],'[Speditiondat.xlsm]Spedition2'!C1:C2,2,0))=TRUE,_
IF(ISNA(VLOOKUP(RC[-6],'[Speditiondat.xlsm]Spedition3'!C1:C2,2,0))=TRUE,_
""Kein Treffer"",""Treffer""),""Treffer""),""Treffer"")"
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

05.09.2019 18:51:10

Werner

Hallo,
stellt sich die Frage, was für dich variabel ist. Irgendwoher muss der Name deiner Datei ja kommen. Entweder du hast die in irgendeiner Zelle stehen auf die du dich in Der Formel beziehst, oder du benutzt eine Variable.
Im Beispiel mal die SVERWEIS-Formel mit dem Namen der Datei aus der Zelle A1. Statt einer Zelle kannst du natürlich auch eine Variable benutzen. Die könntest du z.B. über eine Iputbox füllen. Dann einfach statt .Range("A1") halt den Namen der Variablen einsetzen.

.Range(.Cells(2, "G"), .Cells(Anzahl_Aufträge, "G")).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition1'!C1:C2,2,0))=TRUE," _
& "IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition2'!C1:C2,2,0))=TRUE," _
& "IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition3'!C1:C2,2,0))=TRUE," _
& """Kein Treffer"",""Treffer""),""Treffer""),""Treffer"")"

Eine Formel ist in VBA zunächst mal ein String. Siehst du ja auch an den Gäsefüßchen am Anfang und am Ende. Erst mit dem Eintragen in eine Zelle erkennt Excel an den =, dass es eine Formel ist und wandelt sie entsprechend in eine Formel um.
Wenn du in der Formel Variable oder Zellwerte verwenden willst, dann mußt du Excel mitteilen, wann die Formel (der String) endet. Das macht man mit Gänsefüßchen. Die Variable verküpfst du dann mit & als String mit der Formel. danach muß Excel jetzt wieder wissen, wo es mit der eigentlichen Formel (dem String) weitergeht. Das machst du ebenfalls mit & gefolgt von einem Gänsefüßchen.
Das sieht dann so aus:
,'[" & .Range("A1") & "]Spedition1
Nach der eckigen Klammer endet der eigentliche Formelstring also Gänsefüßchen. Dann wird mit & die Variable bzw. der Zellwert in den Formelstring eingefügt. Danach wird mit & " der eigentliche Formelstring weitergeführt.
Gruß Werner

AW: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

06.09.2019 06:16:34

Jasper

Hallo Werner,
meine Variable für die Bezugsdatei ziehe ich durch "Application.GetOpenFilename()". Sollte doch funktionieren, oder?
Danke für deine ausführliche Erklärung, werde es nachher bei der Arbeit ausprobieren :)
MfG Jasper

AW: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

06.09.2019 08:34:34

Jasper

Hallo Werner,
deine Code Erklärung hat geholfen :)
Die Datei ziehts perfekt Variabel rein, nur die verschiedenen Tabellenblätter muss ich noch lösen.
Ich passe einfach meine Variablen an und statt nur 1 Variable für die Datei mache ich 3 variablen auf die verschiedenen Tabellenblätter.
MfG
Jasper

Gerne u. Danke für die Rückmeldung. o.w.T.

06.09.2019 10:03:34

Werner

Infobox / Tutorial

SVERWEIS mit variabler Bezugs-Datei in VBA

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Dateien auswählen: Verwende Application.GetOpenFilename() um die beiden benötigten Excel-Dateien auszuwählen. Eine für die Exportdaten und eine für die Speditionsinformationen.
```
Export = Application.GetOpenFilename()
DHL_Schenker_Kuehne = Application.GetOpenFilename()
```

Datenbereinigung: Öffne die Exportdatei und entferne Duplikate in den benötigten Spalten.

Workbooks.Open (Export)
With ActiveWorkbook.Sheets(1)
   .Range("$A$1:$F$5000").RemoveDuplicates Columns:=1, Header:=xlYes
End With

SVERWEIS-Formel einfügen: Schreibe die SVERWEIS-Formel in die Zellen, indem du den Bezug zur Speditionsdatei variabel machst. Hier ist ein Beispiel, wie du die Formel gestalten kannst, um die Datei aus einer Zelle oder einer Variablen zu beziehen.

.Range(.Cells(2, "G"), .Cells(Anzahl_Aufträge, "G")).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition1'!C1:C2,2,0))=TRUE," _
& "IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition2'!C1:C2,2,0))=TRUE," _
& "IF(ISNA(VLOOKUP(RC[-6],'[" & .Range("A1") & "]Spedition3'!C1:C2,2,0))=TRUE," _
& """Kein Treffer"",""Treffer""),""Treffer""),""Treffer"")"

Makro ausführen: Starte das Makro, um die SVERWEIS-Formeln einzufügen und die Daten zu aktualisieren.

Häufige Fehler und Lösungen

Fehlerhafte Dateipfade: Stelle sicher, dass der Pfad zur Datei korrekt ist. Verwende Debugging, um den Pfad zu überprüfen.
Variablen nicht erkannt: Achte darauf, dass du die Variablen korrekt definierst und dass sie den gewünschten Wert enthalten, bevor du sie in der Formel verwendest.
Formel funktioniert nicht: Überprüfe die Syntax der SVERWEIS-Formel in VBA. Achte darauf, dass die Variablenverkettung korrekt ist und dass die SVERWEIS-Formel als String behandelt wird.

Alternative Methoden

Direkte Eingabe in Zellen: Anstatt die SVERWEIS-Formel in VBA zu schreiben, kannst du sie auch direkt in den Zellen eingeben und die Datei manuell anpassen, sofern dies praktikabel ist.
Nutzung von Named Ranges: Definiere benannte Bereiche in deinen Excel-Dateien, um die Lesbarkeit und Wartbarkeit der Formeln zu verbessern.

Praktische Beispiele

Hier ist ein vollständiges Beispiel für den Einsatz eines SVERWEIS in VBA mit variablen Bezug:

Sub Zolldaten_aktualisieren()
    Dim Export As Variant
    Dim DHL_Schenker_Kuehne As Variant
    MsgBox "Bitte wähle deine Zieldatei/ den Export", vbInformation
    Export = Application.GetOpenFilename()
    MsgBox "Bitte wähle die Datei, welche die Information von Speditionen enthält", vbInformation
    DHL_Schenker_Kuehne = Application.GetOpenFilename()

    ' Daten vorbereiten
    Application.ScreenUpdating = False
    Workbooks.Open (Export)

    With ActiveWorkbook.Sheets(1)
        .Range("$A$1:$F$5000").RemoveDuplicates Columns:=1, Header:=xlYes
        .Range(.Cells(2, "G"), .Cells(Anzahl_Aufträge, "G")).FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-6],'[" & DHL_Schenker_Kuehne & "]Spedition1'!C1:C2,2,0))=TRUE," _
        & "IF(ISNA(VLOOKUP(RC[-6],'[" & DHL_Schenker_Kuehne & "]Spedition2'!C1:C2,2,0))=TRUE," _
        & "IF(ISNA(VLOOKUP(RC[-6],'[" & DHL_Schenker_Kuehne & "]Spedition3'!C1:C2,2,0))=TRUE," _
        & """Kein Treffer"",""Treffer""),""Treffer""),""Treffer"")"
    End With

    Application.ScreenUpdating = True
End Sub

Tipps für Profis

Nutze Option Explicit, um sicherzustellen, dass alle Variablen deklariert sind. Dies verhindert viele häufige Fehler.
Kommentiere deinen Code, um die Funktionsweise deiner Makros für dich und andere verständlicher zu machen.
Verwende Debug.Print, um Variablenwerte während der Ausführung zu überprüfen und Probleme schneller zu identifizieren.

FAQ: Häufige Fragen

1. Wie kann ich die SVERWEIS-Formel in VBA dynamisch gestalten?
Durch die Verwendung von Variablen und Verkettung kannst du den Dateinamen und die Blätter dynamisch in deine Formel einfügen.

2. Was ist der Unterschied zwischen VLOOKUP und SVERWEIS in VBA?
Es gibt keinen funktionalen Unterschied; VLOOKUP ist die englische Bezeichnung für SVERWEIS. Beide Funktionen erfüllen die gleiche Aufgabe, wobei der Name je nach Sprache variiert.

3. Kann ich mehrere Tabellenblätter in einem SVERWEIS abfragen?
Ja, du kannst mehrere SVERWEIS-Formeln schachteln oder eine Schleife verwenden, um durch die Tabellenblätter zu iterieren und die Ergebnisse zu kombinieren.

Beliebteste Forumthreads (12 Monate)

Über VBA einen SVERWEIS mit variabler Bezugs-Datei

Forumthread: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

SVERWEIS mit variabler Bezugs-Datei in VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Über VBA einen SVERWEIS mit variabler Bezugs-Datei

Forumthread: Über VBA einen SVERWEIS mit variabler Bezugs-Datei

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVERWEIS mit variabler Bezugs-Datei in VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread