Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1520to1524
1520to1524
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA Alternative zu Sverweis

Forumthread: VBA Alternative zu Sverweis

VBA Alternative zu Sverweis
18.10.2016 08:49:07
Josef
Hallo Liebe Gemeinde,
ich bräuchte mal eure Hilfe.
wie kann ich einen Sverweis durch einen VBA ersetzen?
Wenn ich es per Makroaufnahme mache, dann schreibt mir mein Makro logischerweise die Formel in die Zelle. Ich jedoch benötige gleich das Ergebnis als Wert?
Anbei mal eine Musterdatei:
https://www.herber.de/bbs/user/108841.xlsx
Das Ergebnis soll wie folgt aussehen:
Suche in Tabelle1 nach "Artikel_Nr" aus Tabelle2 und gebe in Tabelle2 die Beschreibung aus Tabelle1 als Wert wieder.
Ich hoffe Ihr könnt mir helfen.
MFG
Josef
Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Alternative zu Sverweis
18.10.2016 08:55:59
ChrisL
Hi Josef
Ja, Tabellenfunktionen kann man auch in VBA verwenden:
Sub t()
Dim iSuche As Integer
iSuche = 525
MsgBox WorksheetFunction.VLookup(iSuche, Worksheets("Tabelle1").Range("B10:D15"), 2, 0)
End Sub

SVWEREIS = VLOOKUP
http://www.excel-center.de/excel/tablefktende.php
cu
Chris
Anzeige
AW: VBA Alternative zu Sverweis
18.10.2016 09:04:22
Josef
Sorry Chris,
aber dein Code funktioniert nicht. Ausserdem soll nach allen Werten in Spalte A gesucht werden und nicht nur nach 525 (zumindest verstehe ich so deinen Code - der leider nicht funktioniert).
Bezogen auf...
18.10.2016 09:32:34
Case
Hallo Josef, :-)
... dein Beispiel so: ;-)
Option Explicit
Sub Main()
With Tabelle2.Range("D3:D12")
.Formula = "=IF(C3 """",VLOOKUP(C3,Tabelle1!$B$10:$D$15,3,FALSE),"""")"
.Value = .Value
End With
End Sub
Servus
Case
Anzeige
AW: Bezogen auf...
18.10.2016 10:36:16
Josef
Hallo Case,
dieser Code scheint zu funktionieren (danke schon mal dafür). Der Code funktioniert aber nicht, wenn ich in Tabelle1 einen Tabellenfilter gesetzt habe. Kannst du mir sagen wieso und wie ich den Code umschreiben muss, damit es funktioniert?
AW: Bezogen auf...
18.10.2016 11:43:23
UweD
Hallo
was funktioniert nicht?
Sowohl in Meiner, als auch in der Lösung von Case spielt es keine Rolle, ob in Tabelle1 Zeilen ausgefiltert sind.
Oder möchtest du genau das Gegenteil, dass wenn ausgeblendet ist, auch in Tabelle2 nichts angezeigt wird?
Anzeige
AW: Bezogen auf...
18.10.2016 11:59:52
Josef
Hi!
Sorry da war ich etwas zu schnell...mein Fehler. Ich meinte wenn ich in Tabelle2 einen Filter setze, dann funktioniert es nicht.
AW: Bezogen auf...
18.10.2016 12:12:52
Daniel
Hi
wenn du willst, dass alle Zellen bearbeitet werden solltest du den Filter aufheben.
das macht man mit: ActiveSheet.ShowAllData
da dieser Befehl einen Fehler verursacht, wenn kein Autofilter vorhanden ist, kapselt man in On Error:
On Error Resume Next
ActiveSheet.ShowAllData
On Error Goto 0
das muss vor dem restlichen Code ausgeführt werden.
Gruß Daniel
Anzeige
AW: Bezogen auf...
18.10.2016 12:15:37
Josef
Das könnte funktionieren, aber dann müsste ich wieder in allen Spalten die richtigen Filter setzen, dass kann mühselig werden bei 35 Spalten.
AW: Bezogen auf...
18.10.2016 12:42:43
Daniel
Hi
ok, dann hier mal was, was auch bei ausgeblendeten Zellen funktioniert, dh auch ausgeblendete Zellen werden bearbeitet.
Sub WerteEinfügen()
Dim dicB As Object
Dim arr
Dim z As Long, s As Long
Set dicB = CreateObject("scripting.dictionary")
'--- Einlesen in Dictionary
arr = Sheets("Tabelle1").Range("B9").CurrentRegion.Value
For z = 1 To UBound(arr, 1)
dicB(arr(z, 1)) = arr(z, 3)
Next
'--- Ergebnis ausgeben
With Sheets("Tabelle2")
For z = 3 To .UsedRange.Row + .UsedRange.Rows.Count - 1
If .Cells(z, 3)  "" Then .Cells(z, 6) = dicB(.Cells(z, 3).Value)
Next
End With
End Sub
das Dictionary ist eine alternative zum SVerweis und ist im Prinzip ein eindimensionales Array, bei welchem der Index nicht durch eine feste Ziffernfolge gebildet wird, sondern durch einen beliebigen Freitext.
Wenn das Dictionary erstmal befüllt ist, ist die Suche nach den Einträgen wesentlich schneller als die Suche mit dem SVerweis.
Gruß Daniel
Anzeige
AW: Bezogen auf...
18.10.2016 13:52:14
Josef
Es funktioniert... Ich danke euch.
AW: Bezogen auf...
18.10.2016 12:56:50
UweD
Hallo
dann über Schleife.
Sub Makro2()
    Dim Tb1, TB2, LR&, ZE%, i&
    Set Tb1 = Sheets("Tabelle1")
    Set TB2 = Sheets("Tabelle2")
    ZE = 3 'erste Zeile 
    If TB2.FilterMode Then
        LR = TB2.AutoFilter.Range.Rows.Count + TB2.AutoFilter.Range.Row - 1
    Else
        LR = TB2.Cells.SpecialCells(xlCellTypeLastCell).Row 'Letzte Zeile des gesamten Blattes 
    End If
    For i = ZE To LR
        On Error Resume Next
        TB2.Cells(i, 4) = WorksheetFunction.VLookup(TB2.Cells(i, 3), Tb1.Columns("B:D"), 2, 0)
        TB2.Cells(i, 6) = WorksheetFunction.VLookup(TB2.Cells(i, 3), Tb1.Columns("B:D"), 3, 0)
    Next i
End Sub

LG UweD
Anzeige
AW: VBA Alternative zu Sverweis
18.10.2016 09:32:58
baschti007
Sicher geht der Code nur musst du eine schleife mit einbauen.
Gruß Basti
Sub dd()
Dim rng1 As Range, rng2 As Range, z As Long
Set rng1 = Worksheets("Tabelle1").Range("B9:D15")
Set rng2 = Worksheets("Tabelle2").Range("C3:C12")
On Error Resume Next
For z = 1 To rng2.Columns(1).Rows.Count
Debug.Print rng2.Cells(z, 1)
rng2.Cells(z, 1).Offset(0, 3) = WorksheetFunction.VLookup(rng2.Cells(z, 1), rng1, 3, 0)
Next
End Sub

Anzeige
AW: VBA Alternative zu Sverweis
18.10.2016 09:38:56
UweD
Hallo
in ein Modul..
Sub Makro2()
    Dim Tb1, TB2, LR&, ZE%
    Set Tb1 = Sheets("Tabelle1")
    Set TB2 = Sheets("Tabelle2")
    ZE = 3 'erste Zeile 
    LR = TB2.Cells(TB2.Rows.Count, "C").End(xlUp).Row 'letzte Zeile der Spalte 
    With TB2.Range(Cells(ZE, 4), Cells(LR, 4))
        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC3," & Tb1.Name & "!C2:C4,2,0),"""")"
        .Value = .Value 'in Werte umwandeln 
    End With
    With TB2.Range(Cells(ZE, 6), Cells(LR, 6))
        .FormulaR1C1 = "=IFERROR(VLOOKUP(RC3," & Tb1.Name & "!C2:C4,3,0),"""")"
        .Value = .Value
    End With
End Sub

LG UweD
Anzeige
;

Forumthreads zu verwandten Themen

Mit VBA In Zelle Excelformel schreiben
Gho-Reg

Hi Leute
Ich habe hier diesen netten VBA Code geschrieben und leider wie solls auch anders sein kommt dauernd eine Fehlermeldung :)
CODE:
rowbeforeinteger = 2
Dim text As String
text = "=IF(" & epsoldcolumn & rowbeforeinteger & "=""x"";1;0)+IF(" & emailsoldco...
25.08.2008 11:08:33
Thread-ID: 1004403
6 Beiträge
Alternative zu NV verhindern mit wenn bei sverweis
merlin

Guten Morgen!
um beim Sverweis die Ausgabe von "#NV" zu verhindern (wenn für einzelne Elemente kein Wert hinterlegt ist), gibt es bekannterweise die Möglichkeit:
=wenn(istnv(sverweis(a;b;c;d));"";sverweis(a;b;c;d))
Dies ist zum einen nicht besonders übersichtlich und zum anderen mus...
14.05.2009 08:33:37
Thread-ID: 1074982
8 Beiträge
variabler SVerweis mit VBA
Jonathan

Hallo zusammen,
in einer Tabelle sind verschiedene Zeilen mit einem "x" in Spalte C markiert. Die Anzahl der "x" variert.
Nun versuche ich ein Makro zu erstellen, welches mir, wenn eine Zeile mit einem "x" markiert ist in
Spalte B einen SVerweis (Suchwert steht in Spalte A) einträg...
17.06.2009 14:28:57
Thread-ID: 1082250
3 Beiträge
SVerweis ??? in VBA
kirsche

hallo leute,
mal wieder brauche ich eure hilfe.
ich weiß nicht, ob ich mit dem SVerweis in VBA übehaupt richtig liege. in B1 steht mein 1. suchbegriff (25) und in A3, A5 ... A25 (immer in zweierschritten) steht mein 2. suchbegriff.
nun möchte ich in tabelle "Stammdaten" zuerst die s...
08.07.2009 23:26:22
Thread-ID: 1087239
5 Beiträge
SVerweis in VBA
Stefanglander

Hallo Forum,
ich habe folgenden Code in einer Zelle:
=WENN(C4="";"";SVERWEIS(C4;Stoffplan!$A$2:$F$100;2;FALSCH))
Mag mir jamand sagen wie und wo ich das in VBA hinterlegen kann.
Danke im Vorfelde
Stefan
05.10.2009 21:11:52
Thread-ID: 1107351
11 Beiträge
Anzeige
zu langer SVERWEIS in VBA möglich?
Holger

Hallo liebe Leute,
hab diese Frage schon bei "Office-Lösung" gestellt, mir konnte aber nicht geholfen werden.
Folgendes Problem:
ich möchte mit einem SVERWEIS in ca. 15 verschiedenen Tabellen suchen. In Spalte A ist mein Suchkriterium und in Spalte B ein Begriff (Ort). Ich möchte al...
04.01.2010 09:32:17
Thread-ID: 1126969
14 Beiträge
Formel Sverweis in VBA
Marko Albrecht

Hallo,
ich hoffe mir kann bei meinem, nachfolgend beschriebenen, Problem geholfen werden.Das Problem ist , daß in der nachstehenen Formel(Wenn, Istfehler, Sverweis) VBA ein Hochkomma in die Formel schreibt und die Formel dadurch nicht Ihre Funktion erfüllt.Die Bezugszelle der Formel Sverweis...
11.04.2002 14:27:49
Thread-ID: 112467
10 Beiträge
Formel per VBA in Zelle schreiben
einfachf

Hallo Zusammen,
ich habe folgendes Problem
Vielleicht kann mir ja jemand helfen.
Ich prüfe in welcher Zelle der letzte Wert steht mit
CellCount = Cells(65536, 23).End(xlUp).Row
Nun möchte ich in sagen wir mal Cells(CellCount, 21) die Formel eintragen
Summe aus Cells(Cel...
20.02.2010 14:24:22
Thread-ID: 1139917
7 Beiträge
Sverweis mit VBA funktionier nicht richtig
F.

Hallo zusammen
Ich möchte zu einer Ziffer, welche ab dem zweiten Blatt in A1 steht, die dazugehörige Bezeichnung aus dem ersten Blatt "Konti" (Spalte B) ziehen und diese dann im Feld B1 eintragen. Irgendwas mache ich falsch.
Sub Konten_bezeichnen() Dim i As Long For i = Sheets("Konti")...
10.03.2010 14:58:20
Thread-ID: 1144879
5 Beiträge
SVerweis in VBA umsetzen
Ines

Hallo,
ich habe mir ein Userform erstellt, da ich Daten in Bezug auf das Datum in eine Tabelle eintragen muss.
Mein Problem ist, dass die Daten nicht irgendwo automatisch eingetragen werden sollen.
Ich würde gerne
im Userform 4 Textfelder ausfüllen und das Datum angeben
D...
10.03.2010 21:36:01
Thread-ID: 1145009
9 Beiträge
Autofilter aus VBA liefert kein ergebnis
Karsten


Hallo liebe Forumsgemeinde,
ich habe ein Problem mit dem Autofilter, den ich mit folgendem Code "auslöse":
ActiveSheet.Activate
With ActiveSheet
.AutoFilterMode = False
.Range("A8:H8").AutoFilter
.Range("A8:H8").AutoFilter Field:=1, Criteria1:=">=" & Range("J...
20.03.2010 15:52:25
Thread-ID: 1147277
10 Beiträge
Formel in VBA umsetzen
Josef


Hallo!
Wie würde bitte die untenstehende Formel in VBA umgesetzt lauten?
=RUNDEN(F11*0,2;2)
F11 wäre in diesem Fall eine TextBox in einer UserForm.
Danke
Josef
15.04.2010 07:06:19
Thread-ID: 1151875
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

VBA Alternativen zum Sverweis in Excel

Schritt-für-Schritt-Anleitung

Um einen Sverweis in Excel durch VBA zu ersetzen, kannst du die folgenden Schritte befolgen:

  1. Öffne das Visual Basic for Applications (VBA) Fenster:

    • Drücke ALT + F11 in Excel.

  2. Füge ein neues Modul hinzu:

    • Klicke mit der rechten Maustaste auf "VBAProject (dein Dokumentname)" und wähle "Einfügen" > "Modul".

  3. Schreibe den VBA-Code:

    • Beispiel für einen einfachen Sverweis mit VBA:
    Sub SverweisMitVBA()
   Dim i As Long
   Dim ws1 As Worksheet, ws2 As Worksheet
   Set ws1 = ThisWorkbook.Worksheets("Tabelle1")
   Set ws2 = ThisWorkbook.Worksheets("Tabelle2")

   For i = 3 To ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row
       On Error Resume Next
       ws2.Cells(i, 4).Value = Application.WorksheetFunction.VLookup(ws2.Cells(i, 3).Value, ws1.Range("B10:D15"), 2, False)
       ws2.Cells(i, 6).Value = Application.WorksheetFunction.VLookup(ws2.Cells(i, 3).Value, ws1.Range("B10:D15"), 3, False)
   Next i
End Sub

  4. Führe das Makro aus:

    • Drücke F5 oder klicke auf "Ausführen".

Häufige Fehler und Lösungen

  • Fehler: "Typen inkompatibel"
    Lösung: Stelle sicher, dass du die korrekten Datentypen verwendest. Wenn du nach Zahlen suchst, sollten die Suchwerte und die Werte in der Tabelle als Zahlen formatiert sein.

  • Fehler: "Wert nicht gefunden"
    Lösung: Überprüfe, ob der Wert, den du suchst, tatsächlich in der Suchmatrix vorhanden ist. Achte auf Leerzeichen und Groß-/Kleinschreibung.

  • Problem mit gefilterten Daten
    Wenn du Filter auf einer Tabelle hast, benutze ActiveSheet.ShowAllData, um die Filter aufzuheben, bevor du die Werte bearbeitest:

    On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Alternative Methoden

Wenn du eine schnellere Alternative zum Sverweis benötigst, kannst du ein Dictionary verwenden. Das Dictionary ist besonders nützlich, wenn du mit großen Datenmengen arbeitest:

Sub WerteEinfügen()
    Dim dicB As Object
    Dim arr
    Dim z As Long
    Set dicB = CreateObject("Scripting.Dictionary")

    arr = Sheets("Tabelle1").Range("B9").CurrentRegion.Value
    For z = 1 To UBound(arr, 1)
        dicB(arr(z, 1)) = arr(z, 3)
    Next

    With Sheets("Tabelle2")
        For z = 3 To .UsedRange.Row + .UsedRange.Rows.Count - 1
            If .Cells(z, 3) <> "" Then .Cells(z, 6) = dicB(.Cells(z, 3).Value)
        Next
    End With
End Sub

Praktische Beispiele

Hier sind einige Beispiele, wie du den VBA Sverweis in verschiedenen Szenarien anwenden kannst:

  • Beispiel 1: Suche nach Artikelnummern und gib die Beschreibung zurück.

  • Beispiel 2: Verwende IFERROR in der Formel, um Fehler zu vermeiden:

    With Tabelle2.Range("D3:D12")
   .Formula = "=IFERROR(VLOOKUP(C3,Tabelle1!$B$10:$D$15,3,FALSE),"""")"
   .Value = .Value
End With

Tipps für Profis

  • Verwende das Dictionary: Das ist eine alternative zu Sverweis, die oft schneller ist, besonders bei großen Datenmengen.

  • Optimiere deinen Code: Reduziere die Anzahl der verwendeten Variablen und vermeide die Nutzung von Select oder Activate, um die Ausführungsgeschwindigkeit zu erhöhen.

  • Teste deinen Code: Verwende Debug.Print, um Werte während der Ausführung zu überprüfen, insbesondere bei komplexen Logiken.

FAQ: Häufige Fragen

1. Wie kann ich den Sverweis mit VBA in eine Zelle schreiben?
Du kannst den Wert direkt in die Zelle schreiben, indem du die .Value-Eigenschaft verwendest, wie im obigen Beispiel gezeigt.

2. Gibt es eine alternative zu VBA für Sverweis?
Ja, die Verwendung von Formeln wie XVERWEIS in neueren Excel-Versionen bietet eine sehr flexible und leistungsfähige Alternative zum klassischen Sverweis.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige