Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: SVerweis (VBA) .... mit Schleife Werte kopieren.

SVerweis (VBA) .... mit Schleife Werte kopieren.
27.01.2006 21:44:05
Proxima05
Ich habe folgendes vor. In einem Tabellenbereich (A1:A500) der Tabelle1 gibt es eine Spalte, deren Zellinhalt variabel ist (entweder enthält er "an" oder aus").
Über SVerweis in der Tabelle 2 hole ich mir für jeden Wert "an", die zugehörige Zahl in einer korrespondierenden Spalte. In VBA würde ich das so umsetzen:

Sub WertÜberSVerweis()
Dim v As Worksheet
Set v = Sheets("Tabelle1")
v.Cells(1, 5).Value = _
WorksheetFunction.VLookup(v.Cells(1, 4).Value, v.Range("A1:A500"), 2, False)
End Sub

Ausgabezelle = Tabelle2!A1
Suchkriterium in Tabelle1!D1
End Sub
Der Punkt ist jetzt nur, dass "an" mehrmals in der Tabelle erscheinen kann. Wie muss der Code aussehen, dass die SVerweis-Schleife solange durchlaufen wird, bis alle Positionen mit "an" gefunden worden sind. Und wie würde die Ergänzung aussehen, dass alle gefundenen Werte, dann auch in Tabelle2 untereinander kopiert werden.
Allerbesten Dank für einen Tipp.
Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVerweis (VBA) .... mit Schleife Werte kopiere
27.01.2006 22:41:53
Josef
Hallo Proxima!
Wenn schon mit VBA, dann mit Find()!
Sub WerteMitFind()
Dim objSource As Worksheet, objTarget As Worksheet
Dim rngFind As Range
Dim strFirst As String
Dim varSearch As Variant
Dim lngRow As Long

Set objSource = Sheets("Tabelle1") ' Quelltabelle
Set objTarget = Sheets("Tabelle2") ' Zieltabelle

objTarget.Range("A2:A65536").ClearContents 'Ausgabebereich leeren
varSearch = objSource.Range("D1") 'Suchbegriff
lngRow = 2 'Beginn der Ausgabe in "A2"

Set rngFind = objSource.Range("A:A").Find(What:=varSearch, LookAt:=xlWhole, LookIn:=xlValues)

If Not rngFind Is Nothing Then
  strFirst = rngFind.Address
  
  Do
    objTarget.Cells(lngRow, 1) = rngFind.Offset(0, 1) ' Wert aus Spalte "B"
    lngRow = lngRow + 1
    
    Set rngFind = objSource.Range("A:A").FindNext(rngFind)
    
  Loop While Not rngFind Is Nothing And strFirst <> rngFind.Address
  
End If

End Sub


'******************************
'* Gruß Sepp
'*
'* Rückmeldung wäre nett!
'******************************

Anzeige
AW: SVerweis (VBA) .... mit Schleife Werte kopiere
28.01.2006 20:15:57
proxima05
Vielen Dank an euch beide ..... ich muss das noch probieren, will den Code natürlich auch verstehen.
Melde mich noch!
AW: SVerweis (VBA) .... mit Schleife Werte kopieren.
27.01.2006 22:43:53
Jan3
Hi,
Ohne SVerweis so, wenn ich Dich richtig verstanden habe.

Sub Übertrag()
Dim z, s As Long
Dim ws, ws1 As Worksheet
Set ws = Worksheets("Tabelle1")
Set ws1 = Worksheets("Tabelle2")
anz = ws.Cells(65536, 1).End(xlUp).Row
z1 = 1
For z = 1 To anz
If ws.Cells(z, 1) = "an" Then
ws1.Cells(z1, 1) = ws.Cells(z, 2)
z1 = z1 + 1
End If
Next
End Sub

Jan
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

SVerweis in VBA umsetzen: Werte mit Schleife kopieren


Schritt-für-Schritt-Anleitung

Um den SVerweis in VBA umzusetzen und Werte aus einer Tabelle zu kopieren, kannst Du folgende Schritte befolgen:

  1. Öffne den VBA-Editor in Excel mit ALT + F11.
  2. Erstelle ein neues Modul: Klicke mit der rechten Maustaste auf "VBAProject (DeineDatei.xlsx)" > Einfügen > Modul.
  3. Füge den folgenden Code ein:
Sub WertÜberSVerweis()
    Dim v As Worksheet
    Dim lngRow As Long
    Set v = Sheets("Tabelle1")

    ' Definiere die Ausgangszelle in Tabelle2
    Dim outputRow As Long
    outputRow = 1

    ' Durchlaufe die Spalte A bis zur letzten Zeile mit Daten
    For lngRow = 1 To 500
        If v.Cells(lngRow, 1).Value = "an" Then
            Sheets("Tabelle2").Cells(outputRow, 1).Value = _
                WorksheetFunction.VLookup(v.Cells(lngRow, 4).Value, v.Range("A1:B500"), 2, False)
            outputRow = outputRow + 1
        End If
    Next lngRow
End Sub
  1. Schließe den VBA-Editor und kehre zu Excel zurück.
  2. Führe das Makro aus: Gehe zu "Entwicklertools" > "Makros", wähle WertÜberSVerweis aus und klicke auf "Ausführen".

Dieser Code sucht in Tabelle1 nach dem Wert "an" in Spalte A und kopiert die entsprechenden Werte in Tabelle2.


Häufige Fehler und Lösungen

  • Fehler: Laufzeitfehler 1004: Dieser Fehler tritt auf, wenn der SVerweis einen Wert nicht findet. Stelle sicher, dass die Daten in der Suchmatrix korrekt sind.
  • Lösung: Verwende die If Not IsError(...)-Anweisung, um zu überprüfen, ob der SVerweis erfolgreich war.
If Not IsError(WorksheetFunction.VLookup(...)) Then
    ' Dein Code
End If

Alternative Methoden

Eine Alternative zum SVerweis in VBA ist die Verwendung der Find()-Methode. Diese Methode ist oft schneller, insbesondere bei großen Datenmengen.

Hier ein Beispiel:

Sub WerteMitFind()
    Dim objSource As Worksheet
    Dim objTarget As Worksheet
    Dim rngFind As Range
    Dim varSearch As String
    Dim lngRow As Long

    Set objSource = Sheets("Tabelle1")
    Set objTarget = Sheets("Tabelle2")
    objTarget.Range("A2:A65536").ClearContents

    varSearch = "an"
    lngRow = 2

    Set rngFind = objSource.Range("A:A").Find(What:=varSearch, LookAt:=xlWhole, LookIn:=xlValues)

    If Not rngFind Is Nothing Then
        Do
            objTarget.Cells(lngRow, 1) = rngFind.Offset(0, 1) ' Wert aus Spalte B
            lngRow = lngRow + 1
            Set rngFind = objSource.Range("A:A").FindNext(rngFind)
        Loop While Not rngFind Is Nothing
    End If
End Sub

Praktische Beispiele

  • Verwendung von SVerweis in VBA:

    • Wenn Du den SVerweis in VBA nutzt, um Werte aus einer Tabelle zu kopieren, kannst Du sicherstellen, dass die Daten in der richtigen Reihenfolge angeordnet sind, um Fehler zu vermeiden.
  • VBA SVerweis in großen Datenmengen:

    • Bei großen Datenmengen ist es oft effizienter, die Find()-Methode zu verwenden, da sie schneller ist als die Schleife mit dem SVerweis.

Tipps für Profis

  • Nutze die Option Explicit-Anweisung am Anfang Deines Moduls, um sicherzustellen, dass alle Variablen deklariert sind. Das reduziert Fehler.
  • Kommentiere Deinen Code gründlich, damit Du und andere den Code später leichter verstehen können.
  • Wenn Du den SVerweis in VBA anwendest, achte darauf, die Spaltenindices korrekt zu setzen, um die gewünschten Werte zu extrahieren.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen SVerweis und Find()? Der SVerweis ist eine Funktion, die einen bestimmten Wert in einer Spalte sucht und einen entsprechenden Wert aus einer anderen Spalte zurückgibt. Die Find()-Methode hingegen durchläuft die Daten schneller und kann mehrere Vorkommen des Suchkriteriums finden.

2. Wie kann ich sicherstellen, dass der SVerweis Werte korrekt findet? Stelle sicher, dass die Daten in der Suchmatrix sortiert sind und dass Du die richtigen Parameter im SVerweis verwendest. Überprüfe auch, ob die Zellen keine führenden oder nachfolgenden Leerzeichen enthalten.

3. Kann ich den SVerweis in einer Schleife verwenden? Ja, Du kannst den SVerweis in einer Schleife verwenden, um mehrere Übereinstimmungen zu finden und die Ergebnisse in eine andere Tabelle zu kopieren.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige