Syntax für vba SVERWEIS

Forumthread: Syntax für vba SVERWEIS

04.03.2005 10:05:40

Harald

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

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Syntax für vba SVERWEIS

04.03.2005 12:17:18

Martin
AW: Syntax für vba SVERWEIS

04.03.2005 13:46:59

Harald
AW: Syntax für vba SVERWEIS

04.03.2005 13:58:14

Martin
AW: Syntax für vba SVERWEIS

04.03.2005 14:21:58

Harald
Lade mal die Datei hoch (oT)

05.03.2005 16:52:46

Martin
Datei

07.03.2005 07:25:00

Harald
AW: Lösung

07.03.2005 16:28:21

Martin
AW: Lösung

08.03.2005 07:45:15

Harald

AW: Syntax für vba SVERWEIS

04.03.2005 12:17:18

Martin

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

AW: Syntax für vba SVERWEIS

04.03.2005 13:46:59

Harald

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

AW: Syntax für vba SVERWEIS

04.03.2005 13:58:14

Martin

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

AW: Syntax für vba SVERWEIS

04.03.2005 14:21:58

Harald

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

Lade mal die Datei hoch (oT)

05.03.2005 16:52:46

Martin

Gruß
Martin Beck

Datei

07.03.2005 07:25:00

Harald

Moin Martin,
hier ist eine Musterdatei.
Linkseitig die Quelldaten, rechtseitig soll die Zusammenfassung stehen.
https://www.herber.de/bbs/user/19294.xls,
Gruß
Harald

AW: Lösung

07.03.2005 16:28:21

Martin

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

AW: Lösung

08.03.2005 07:45:15

Harald

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

Beliebteste Forumthreads (12 Monate)

Forumthread: Syntax für vba SVERWEIS

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread