SVerweis als VBA

Forumthread: SVerweis als VBA

SVerweis als VBA

12.08.2015 17:37:25

Ali

Hallo zusammen,
ich hoffe ich bin hier richtig. Die Anfrage wurde sicherlich schon 1000 gestellt, aber finde die für mich angepasste Lösung nicht.
ich möchte einen SVerweis als VBA programieren. Leider funktioniert es noch nicht 100 %.
Folgenden Code habe ich per Macroaufzeichnung gebastet.

Sub Makro5() ' Makro5 Makro ActiveCell.FormulaR1C1 ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C4").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C5").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C7").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C8").Select Application.CutCopyMode = False End Sub

Jetzt möchte ich nicht für jede Zeile die Formel Neu schreiben sondern das immer die nächste Zeile genommen wird und zwar von C1 runter bis beispiel C500.
Könnt Ihr mir helfen, wie ich die Schleife dort reinbekomme?
Vielen Dank

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: SVerweis als VBA

12.08.2015 17:44:53

AlexG
AW: SVerweis als VBA

12.08.2015 18:16:17

Ali
SVerweis Bereich einschränken

12.08.2015 18:52:45

AlexG

AW: SVerweis als VBA

12.08.2015 17:44:53

AlexG

Hallo Ali,
der Makrorecoder zeichnet immer das Selktieren der Zellen mit auf, das brauchst du aber nicht.
In diesem Fall anstelle ActiveCell einfach den Bereich setzten.

Range("C1:C500").FormulaR1C1 = "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)"

Gruß
Alex

AW: SVerweis als VBA

12.08.2015 18:16:17

Ali

Hallo,
super danke für die Antwort.

Sub Makro5() ' Makro5 Makro Range("C1:C50").FormulaR1C1 = "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C4").Select Application.CutCopyMode = False End Sub

es funktioniert super, allerdings funktioniert das kopieren und einfügen nicht mehr. Grund der ganzen Formel war das ich einen Liste habe und einen SVERWEIS eifügen wollte, weil mir die Berechnung viel zu lange gedauert hat, hatte ich überelgt das ganze als VBA Code einzutragen, danach das Ergebnis zu kopieren und als Wert wieder einfügen zu lassen. Das hat auch alles super geklappt und war super schnell, nun funktioniert das Copy Past nicht mehr. Bringt das überhaupt was mit der Schnelligkeit? also anstatt den Sverweis als Formel diesen als VBA zu hinterlegen.
LG

SVerweis Bereich einschränken

12.08.2015 18:52:45

AlexG

Hallo Ali,
du solltest lieber deinen Bereich eingrenzen. Und nicht die ganzen Spalten als Matrix angeben.
VBA wird hier sicherlich nicht schneller sein, wenn der Sverweis eingetragen und das Ergebnis als Festwert übernommen wird.
Gruß
Alex

Infobox / Tutorial

SVerweis in VBA: Schritt-für-Schritt-Anleitung

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

Um einen SVerweis in VBA zu implementieren, folge diesen Schritten:

Öffne den VBA-Editor: Drücke ALT + F11, um den Visual Basic for Applications (VBA) Editor zu öffnen.
Erstelle ein neues Modul: Klicke mit der rechten Maustaste auf "VBAProject (DeineDatei.xlsx)" und wähle Einfügen -> Modul.
Schreibe den folgenden Code:

Sub SVerweisInVBA()
    Range("C1:C500").FormulaR1C1 = "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)"
    Range("C1:C500").Value = Range("C1:C500").Value ' Werte einfügen
End Sub

Führe das Makro aus: Gehe zurück zu Excel, drücke ALT + F8, wähle SVerweisInVBA und klicke auf Ausführen.

Häufige Fehler und Lösungen

Problem: "Das Kopieren und Einfügen funktioniert nicht mehr."
- Lösung: Stelle sicher, dass du den Bereich mit .Value überschreibst, um die Formeln durch Werte zu ersetzen. Siehe Beispiel oben.
Problem: "Der SVerweis liefert falsche Ergebnisse."
- Lösung: Überprüfe, ob der Bereich in der VLOOKUP-Formel korrekt angegeben ist und die Daten in Tabelle1 vorhanden sind.

Alternative Methoden

Eine alternative Methode, um einen SVerweis in VBA zu implementieren, ist die Verwendung einer Schleife. Hier ist ein Beispiel:

Sub SVerweisMitSchleife()
    Dim i As Integer
    For i = 1 To 500
        Cells(i, 3).FormulaR1C1 = "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)"
    Next i
    ' Werte einfügen
    Range("C1:C500").Value = Range("C1:C500").Value
End Sub

Dieses Makro setzt die VLOOKUP-Formel in jede Zelle von C1 bis C500 und ersetzt sie anschließend durch die berechneten Werte.

Praktische Beispiele

Wenn du die Formel anpassen möchtest, um einen bestimmten Bereich zu berücksichtigen, kannst du die Zellen wie folgt anpassen:

Sub SVerweisEingrenzen()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DeinBlattname")

    ws.Range("C1:C50").FormulaR1C1 = "=VLOOKUP(RC[1],'[Test Datei.xlsx]Tabelle1'!C1:C2,2,FALSE)"
    ws.Range("C1:C50").Value = ws.Range("C1:C50").Value
End Sub

Achte darauf, die Arbeitsblattnamen korrekt anzugeben.

Tipps für Profis

Verwende Option Explicit am Anfang des Moduls, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
Versuche, den Bereich der VLOOKUP-Formel so klein wie möglich zu halten, um die Berechnungszeit zu optimieren.
Du kannst Application.ScreenUpdating = False verwenden, um die Bildschirmaktualisierung während des Makros zu deaktivieren, was die Ausführungsgeschwindigkeit erhöht. Vergiss nicht, es am Ende des Makros wieder auf True zu setzen.

FAQ: Häufige Fragen

1. Frage
Was ist der Unterschied zwischen VLOOKUP und SVerweis in VBA?
Antwort: Es gibt keinen Unterschied; es handelt sich nur um die englische und deutsche Bezeichnung der gleichen Funktion.

2. Frage
Kann ich VBA verwenden, um SVerweis in mehreren Blättern auszuführen?
Antwort: Ja, du kannst in deinem VBA-Code auf mehrere Blätter zugreifen, indem du sie entsprechend referenzierst, z. B. Worksheets("Blattname").

Beliebteste Forumthreads (12 Monate)