Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 12:38:40
Formeländerung durch Wechsel von Excel 2021 auf 2019
24.04.2024 12:31:18
Excel - Sverweis mit Wenn-Abfrage kombinieren
24.04.2024 12:16:26
Name von aktuellem VBA-Modul ausgeben
24.04.2024 12:07:15
Werte einer Liste vergleichen und markieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1520to1524
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1520to1524
1520to1524
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

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

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
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).
Anzeige
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
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
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.
Anzeige
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

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

217 Forumthreads zu ähnlichen 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
Anzeige
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
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
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
7
2
4
10
13
9
6
4
8
9
2
1
5
4
3
6
2
2
2
8
3
4
6
6