Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen

vlookup mehrere Tabellen durchsuchen | Herbers Excel-Forum


Betrifft: vlookup mehrere Tabellen durchsuchen von: Stefan
Geschrieben am: 30.12.2009 22:04:35

Hallo zusammen,

trotz Suche in den Archivlisten habe ich zu meinem Problem noch keine Lösung gefunden.

Ich möchte einer Liste von Lieferantennummern in Spalte A mittels VBA (vlookup?) von einer anderen Tabelle in Spalte B die Lieferantennamen zuordnen. Wird dort nichts gefunden, sollen die Werte aus einer anderen Tabelle gelesen werden.

Leider liest mein Makro aber die Daten von der anderen Tabelle nicht:




Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim x1 As Byte
    Dim x2 As Integer
    On Error Resume Next

     x1 = Range("a1").Row
     x2 = Range("a2").End(xlDown).Row

   Range("B1").Activate

   For i = x1 To x2
      ' ActiveCell.ClearContents 'eventuell ausschalten!
         If WorksheetFunction.IsNA(WorksheetFunction.VLookup(Cells(i, 1), Worksheets("Tabelle1") _
.Range("D:E"), 2, False)) = True Then
            Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Worksheets("Tabelle2").Range(" _
A:B"), 2, False)
         Else
            Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Worksheets("Tabelle1").Range(" _
D:E"), 2, False)
         End If
      ActiveCell.Offset(1, 0).Select
   Next i

End Sub


Möglicherweise ist diese Problemstellung für meine ersten VBA-Gehversuche zu komplex. Vielleicht hat aber schon mal jemand so etwas gemacht und kann mir ein Beispiel zur Verfügung stellen?

Per excel-Formel ginge das so:
=WENN(ISTNV(SVERWEIS(A1;Tabelle1'!D:E;2;FALSCH))=WAHR;SVERWEIS(A1;'Tabelle2'!A:B;2;FALSCH);SVERWEIS(A1;'Tabelle1'!A:B;2;FALSCH))

Danke!

Stefan

  

Betrifft: AW: vlookup mehrere Tabellen durchsuchen von: Josef Ehrensberger
Geschrieben am: 30.12.2009 22:20:47

Hallo Stefan,

Private Sub CommandButton1_Click()
  Dim lngIndex As Long, lngFirstRow As Long, lngLastRow As Long
  Dim vntRet As Variant
  
  lngFirstRow = Range("a1").Row
  lngLastRow = Range("a2").End(xlDown).Row
  
  With Application
    For lngIndex = lngFirstRow To lngLastRow
      vntRet = .VLookup(Me.Cells(lngIndex, 1), Worksheets("Tabelle1").Range("D:E"), 2, 0)
      If IsError(vntRet) Then
        vntRet = .VLookup(Me.Cells(lngIndex, 1), Worksheets("Tabelle2").Range("A:B"), 2, 0)
      End If
      If IsError(vntRet) Then
        Me.Cells(lngIndex, 2) = ""
      Else
        Me.Cells(lngIndex, 2) = vntRet
      End If
    Next
  End With
End Sub



Gruß Sepp



  

Betrifft: AW: Formel per Makro einfügen von: Daniel
Geschrieben am: 31.12.2009 00:39:36

Hi

wenn du eine Formel hast, die funktioniert, dann kannst du doch einfach auch diese Formel per Makro einfügen und brauchst das ganze nicht aufwendig per VBA nachzuprogrammieren.
Meistens sind Formellösungen auch schneller als VBA (zumindest muss man tief in die Trickkiste greifen, um per VBA schneller zu sein als eine Formellösung):

Sub Test()
With Range(Cells(1, 2), Cells(1,1).End(xldown).Offset(0,1))
.FormulaLocal = "=WENN(ISTNV(SVERWEIS(A1;Tabelle1'!D:E;2;FALSCH))=WAHR;SVERWEIS(A1;'Tabelle2'!A: _
B;2;FALSCH);SVERWEIS(A1;'Tabelle1'!A:B;2;FALSCH))"
.Formula = .Value
End With
End Sub
Gruß, Daniel
ps. meistens ist es besser, die FormulaR1C1-Variante zu verwenden statt FormulaLocal.
den Formeltext kann man leicht selber rausfinden, wenn man die Formel normal in Excel eingibt und dann im VBA-Editor ?Selection.FormulaR1C1 eintippt. Das Ergebnis kann man dann in den Makrocode kopieren. Ggf vorhandene Anführungszeichen, die zur Formel gehören müssen aber noch verdoppelt werden.


  

Betrifft: AW: vlookup mehrere Tabellen durchsuchen von: Stefan
Geschrieben am: 31.12.2009 10:35:37

Hallo Sepp und Daniel,

vielen Dank für eure Unterstützung.

Der Code von Sepp funktioniert jetzt:

  • Option Explicit
    Private Sub CommandButton1_Click()
        Dim FirstRow As Integer
        Dim LastRow As Byte
        Dim i As Integer
        Dim Ret As Variant
             
        FirstRow = Range("a1").Row
        LastRow = Range("a2").End(xlDown).Row
       
       With Application
          For i = FirstRow To LastRow
             Ret = .VLookup(Me.Cells(i, 1), Worksheets("Tabelle1").Range("D:E"), 2, 0)
             If IsError(Ret) Then
                Ret = .VLookup(Me.Cells(i, 1), Worksheets("Tabelle2").Range("A:B"), 2, 0)
             End If
             If IsError(Ret) Then
                Me.Cells(i, 2) = ""
             Else
                Me.Cells(i, 2) = Ret
             End If
          Next
       End With
    End Sub


  • Den Vorschlag von Daniel werde ich bei den nächsten Problemstellungen ausprobieren.

    Jetzt muss ich erst versuchen, das Ganze so zu verstehen, dass ich eigene Codes schreiben kann. Bisher habe ich ja immer nur Beispiele aus dem Internet umgebaut.

    Ich wünsche euch ein gesundes neues Jahr!

    Viele Grüße

    Stefan


      

    Betrifft: AW: vlookup mehrere Tabellen durchsuchen von: Stefan
    Geschrieben am: 31.12.2009 10:40:54

    Hallo Sepp und Daniel,

    vielen Dank für eure Unterstützung.

    Der Code von Sepp funktioniert jetzt:

  • Option Explicit
    Private Sub CommandButton1_Click()
        Dim FirstRow As Integer
        Dim LastRow As Byte
        Dim i As Integer
        Dim Ret As Variant
             
        FirstRow = Range("a1").Row
        LastRow = Range("a2").End(xlDown).Row
       
       With Application
          For i = FirstRow To LastRow
             Ret = .VLookup(Me.Cells(i, 1), Worksheets("Tabelle1").Range("D:E"), 2, 0)
             If IsError(Ret) Then
                Ret = .VLookup(Me.Cells(i, 1), Worksheets("Tabelle2").Range("A:B"), 2, 0)
             End If
             If IsError(Ret) Then
                Me.Cells(i, 2) = ""
             Else
                Me.Cells(i, 2) = Ret
             End If
          Next
       End With
    End Sub


  • Den Vorschlag von Daniel werde ich bei den nächsten Problemstellungen ausprobieren.

    Jetzt muss ich erst versuchen, das Ganze so zu verstehen, dass ich eigene Codes schreiben kann. Bisher habe ich ja immer nur Beispiele aus dem Internet umgebaut.

    Ich wünsche euch ein gesundes neues Jahr!

    Viele Grüße

    Stefan


    Beiträge aus den Excel-Beispielen zum Thema "vlookup mehrere Tabellen durchsuchen"