Syntax für vba SVERWEIS

Bild

Betrifft: Syntax für vba SVERWEIS
von: Harald E
Geschrieben am: 04.03.2005 10:05:40
Hallo Forum,
per Spezialfilter entferne ich mir aus einen variablen Liste die doppelten Einträge.
Danach brauche ich per SVerweis weitere Daten, die neben den gefilterten Daten stehen sollen. Wie sage ich dem Editor, dass er die SVERWEIS-Formel bis zur letzten gefüllten Zeile runterzieht ?
Hier mein Ansatz


Sub Makro1()
Dim lRowA As Long, lRowB As Long
'Dim bereich1 As Integer, bereich2 As Integer, bereich3 As Integer
lRowA = Cells(Rows.Count, 3).End(xlUp).Row
    Range(Cells(1, 3), Cells(lRowA, 3)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "M1"), Unique:=True
    
lRowB = Cells(Rows.Count, 13).End(xlUp).Row
bereich1 = Range("C2:D" & lRowB)
    
    Range("N2") = "=VLOOKUP(M2,bereich1 ,2,0)"
    With Range("N2:N" & lRowB)
      .FillDown
      .ColumnWidth = 41
   End With
End Sub

Vielen Dank für Tipps
Harald
Bild

Betrifft: AW: Syntax für vba SVERWEIS
von: Martin Beck
Geschrieben am: 04.03.2005 12:17:18
Hallo Harald,
wenn ich Dich recht verstehe, kannst Du die Formel doch kopieren. Ungetestet:
Range("N2").Copy Destination:= Range("N3:N" & lRowB)
Gruß
Martin Beck
Bild

Betrifft: AW: Syntax für vba SVERWEIS
von: Harald E
Geschrieben am: 04.03.2005 13:46:59
Hallo Martin,
sowohl filldown als auch copy destination bringen mir in der Tabelle nur #Name? als Ergebnis.
Schätze das Problem liegt in der Formel selbst.
bereich1 = Range("C2:D" & lRowB)
Range("N2") = "=VLOOKUP(M2,bereich1 ,2,0)"
Habs schon mit bereich1 as range, bzw integer probiert (Laufzeitfehler91 Objektvariable nicht festgelegt.
Die 0 in der Formel gegen false getauscht (#Name? als ergebnis)
Hab keine weitere Idee
Gruß
Harald
Bild

Betrifft: AW: Syntax für vba SVERWEIS
von: Martin Beck
Geschrieben am: 04.03.2005 13:58:14
Hallo Harald,
den Rest des Codes hatte ich mir gar nicht angesehen. So geht's:

Sub test()
Dim lRowA As Long, lRowB As Long
lRowB = Cells(Rows.Count, 13).End(xlUp).Row
bereich1 = Range("C2:D" & lRowB).Address
    
    Range("N2") = "=VLOOKUP(M2, " & bereich1 & " ,2,0)"
    With Range("N2:N" & lRowB)
      .FillDown
      .ColumnWidth = 41
   End With
End Sub

Gruß
Martin Beck
Bild

Betrifft: AW: Syntax für vba SVERWEIS
von: Harald E
Geschrieben am: 04.03.2005 14:21:58
Hallo Martin,
in der Tat, so gehts prima.
1 Problem stellt sich noch. Die Quelldaten weisen in der Regel eine Leerzeile auf.
Selbst wenn ich diese Leerzelle mit einer Null fülle, wird diese 0 zwar mit dem Spezialfilter mit rüberkopiert, doch genau ab dieser Zeile ergibt der Sverweis (in der gefilterten Tabelle) #NV als Ergebnis aus (auch für die folgenden Zeilen, die regulär gefüllt sind).
Hab gleich Feierabend und kann das Problem erst wieder Montag in Angriff nehmen.
Wird wohl nix Gravierendes sein, aber vielleicht fällt dir spontan dazu was ein.
Vielen, vielen Dank für die Syntax- Hilfe
Harald
Bild

Betrifft: Lade mal die Datei hoch (oT)
von: Martin Beck
Geschrieben am: 05.03.2005 16:52:46
Gruß
Martin Beck
Bild

Betrifft: Datei
von: Harald E
Geschrieben am: 07.03.2005 07:25:00
Moin Martin,
hier ist eine Musterdatei.
Linkseitig die Quelldaten, rechtseitig soll die Zusammenfassung stehen.
https://www.herber.de/bbs/user/19294.xls,
Gruß
Harald
Bild

Betrifft: AW: Lösung
von: Martin Beck
Geschrieben am: 07.03.2005 16:28:21
Hallo Harald,
das Problem liegt in der Zeile
lRowB = Cells(Rows.Count, 13).End(xlUp).Row
Damit ermitteltst Du die letzte besetze Zelle in Spalte M und die befindet sich in Zeile 16. Daher wird die Suchmatrix in SVERWEIS auf C2:D16 festgelegt, obwohl der relevante Bereich C2:D26 wäre. Alles was unter C16 steht wird nicht gefinden.
Versuche mal
lRowB = Cells(Rows.Count, 3).End(xlUp).Row
Gruß
Martin Beck
Bild

Betrifft: AW: Lösung
von: Harald E
Geschrieben am: 08.03.2005 07:45:15
Hallo Martin,
vielen Dank. Habe für bereich1 statt lRowB nun lRowA eingesetzt. Funktioniert bestens.
Hier der Code (fürs Archiv) ;->>

Sub Makro2()
Dim lRowA As Long, lRowB As Long
lRowA = Cells(Rows.Count, 3).End(xlUp).Row
    Range(Cells(1, 3), Cells(lRowA, 3)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "M1"), Unique:=True
    
lRowB = Cells(Rows.Count, 13).End(xlUp).Row
bereich1 = Range("C2:D" & lRowA).Address
    
    Range("N2") = "=VLOOKUP(M2, " & bereich1 & " ,2,0)"
    With Range("N2:N" & lRowB)
      .FillDown
      .ColumnWidth = 41
   End With
    Range("O1") = "Stück"
    Range("P1") = "Kosten"
    
    Range("P2").FormulaR1C1 = "=SUMIF(R2C3:R26C3,RC[-3],R2C9:R26C9)"
    Range("P2").AutoFill Destination:=Range("P2:P16"), Type:=xlFillDefault
    
    Range("O2").FormulaR1C1 = "=SUMIF(R2C3:R26C3,RC[-2],R2C8:R26C8)"
    Range("O2").AutoFill Destination:=Range("O2:O16"), Type:=xlFillDefault
    
    Range("M1:P16").Copy
    Range("M1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Gruß
Harald
 Bild

Beiträge aus den Excel-Beispielen zum Thema "Syntax für vba SVERWEIS"