Microsoft Excel

Herbers Excel/VBA-Archiv

Sverweis via VBA

Betrifft: Sverweis via VBA von: Matthias
Geschrieben am: 23.10.2020 10:58:24

Hallo zusammen,

ich habe per VBA einen Sverweis erstellt,
jedoch funktioniert dieser nicht und zieht sich die falschen Werte.

Der Code:

Sub Test()

Dim ko1 As Long
Dim ko2 As Long
Dim ko1h As String
Dim ko2h As String
Dim az As Long
Dim lz2 As Long
Dim lz3 As Long
Dim az2 As Long
Dim koordinate As String
Dim koordinate1 As String
Dim Koordinate2 As String

ko1 = 18
ko2 = 19

Sheets(1).Select
Cells(1, 79).Value = "Hilfsko_alt"
Cells(1, 80).Value = "Hilfsko_neu"

az = 2
lz2 = Cells(Rows.Count, 1).End(xlUp).Row

Do Until az > lz2
ko1h = Format(Cells(az, ko1).Value, "00")
ko2h = Format(Cells(az, ko2).Value, "00")

Cells(az, 79).Value = ko1h & "-" & ko2h
Cells(az, 80).Value = WorksheetFunction.VLookup(Cells(az, 79).Value, Worksheets("Fachklassen"). _
Range("A1:H2619"), 5)
az = az + 1
Loop

lz3 = Cells(Rows.Count, 1).End(xlUp).Row
az2 = 2

Do Until az2 > lz3
koordinate = Cells(az2, 80).Value
koordinate1 = Left(koordinate, 2)
Koordinate2 = Right(koordinate, 2)
Cells(az2, ko1).Value = koordinate1
Cells(az2, ko2).Value = Koordinate2
az2 = az2 + 1
Loop

End Sub
Eigentlich sollte der Wert [Cells(az, 79).Value] im zweiten Tabellenblatt gefunden werden,
aus der gleichen Zeile sollte das Makro dann den Wert der 5ten Spalte übernehmen und im ersten Tabellenblatt wieder in
[Cells(az, 80).Value] eintragen.

Das Makro zieht jedoch eine andere Zahl.

Kann mir jemand den Fehler erläutern?

Beispieldatei: https://www.herber.de/bbs/user/141037.xlsm

Vielen Dank und beste Grüße

Matthias

Betrifft: AW: Sverweis via VBA
von: Regina Resch-Jansen
Geschrieben am: 23.10.2020 11:18:31

Hi,

ohne getestet zu haben, könnte der Klassiker sein: teste mal so:

WorksheetFunction.VLookup(Cells(az, 79).Value, Worksheets("Fachklassen").Range("A1:H2619"), 5,0)

Gruß Regina

Betrifft: AW: Sverweis via VBA
von: Matthias
Geschrieben am: 23.10.2020 11:43:46

Hi Regina,

funktioniert & danke :)

Betrifft: AW: Sverweis via VBA
von: UweD
Geschrieben am: 23.10.2020 11:25:04

Hallo

die Eingabe 03-05 wird von excel als Datum angesehen und in 05.03.2020 umgewandelt


Versuch es mal so
...
Cells(az, 79).Value = "'" & ko1h & "-" & ko2h
Cells(az, 80).Value = "'" & WorksheetFunction.VLookup(Cells(az, 79).Value, Worksheets(" _
Fachklassen").Range("A1:H2619"), 5, 0)
...
Wichtig ist auch, den 4. Parameter beim Sverweis auf false (= 0) zu setzen, da die Liste nicht sortiert ist.


LG UweD

Betrifft: AW: Sverweis via VBA
von: Werner
Geschrieben am: 23.10.2020 11:34:36

Hallo Uwe,

ich habe es jetzt nicht getestet aber ging das denn nicht auch mit .Text statt .Value?
Cells(az, 80).Value = WorksheetFunction.VLookup(Cells(az, 79).Text, Worksheets("Fachklassen").Range("A1:H2619"), 5, 0)
Gruß Werner

Betrifft: AW: Sverweis via VBA
von: Matthias
Geschrieben am: 23.10.2020 11:46:07

Hallo zusammen,

die Zellen sind im Gesamtcode eigentlich als Text formatiert, die entsprechende Zeile hab ich für das erstellen der Beispieldatei wohl gelöscht.

Das mit dem 4ten Parameter hat geholfen. Ich dachte fälschlicherweise bisher, dass dieser etwas anderes aussagt.

Vielen Dank und liebe Grüße

Betrifft: AW: Sverweis via VBA
von: Daniel
Geschrieben am: 23.10.2020 11:31:58

Hi
der SVerweis hat zwei Varianten:
Variante a) für unsortierte Daten
Variante b) für sortierte Daten
welche verwendet wird, steuert man über den 4. Parameter.
ist der 4. Parameter 0 oder FALSCH, wird die Variante für unsortierte Daten angewendet.
ist der 4. Parameter 1 oder WAHR oder man gibt ihn gar nicht an (so wie du!), dann wird die Variante für sortierte Daten angewendet.

allerdings liefert die Variante für sortierte Daten auch mit unsortierten Daten ein Ergebnis, dieses ist aber Unsinn.
Daher sollte man beim SVerweis den 4. Parameter immer mit angeben.
Die Lösung hat dir ja Regina schon gezeigt.

btw es ist bei solchen Aufgaben immer schneller, die Formel in die Zellen zu schreiben, und zwar wenn möglich in alle Zellen gleichzeitig.
Das ist schneller, als für jede Zelle einzeln den SVerweis zu berechnen und jede Zelle einzeln zu befüllen (das ist so, wie wenn beim Bäcker der Mensch vor dir in der Schlange nicht alle Brötchen zusammen bezahlt, sondern jedes einzeln, und für jedes die Münzen aus dem Geldbeutel hervorkramt und sich für jedes Brötchen einen Kassenzettel geben lässt)

Gruß Daniel

Betrifft: AW: Sverweis via VBA
von: Matthias
Geschrieben am: 23.10.2020 11:50:02

Hi Daniel,

danke für die Erläuterung :-)
Habe den Punkt mit dem 4ten Parameter bisher immer anders verstanden.

zum zweiten Punkt.
Du meinst also über das befüllen der oberen Zeile und dem Autorange bis nach unten, oder wie genau?
Ich hatte bisher die Erfahrung gemacht, dass VBA mir schneller Werte liefert, als der "normale" Sverweis innerhalb von Excel

LG

Betrifft: AW: Sverweis via VBA
von: Daniel
Geschrieben am: 23.10.2020 12:24:04

Hi
die Erfahrung, dass der VBA-SVerweis schneller ist das der Excel-Sverweis habe ich bisher nicht gemacht
(gleiche Einstellungen vorausgesetzt)
ein enormer Geschwindigkeitsunterschied besteht zwsichen der sortierten und unsortierten Variante, weswegen es bei großen Datenmengen oft hilfreich ist, zuerst zu sortieren, um die schnelle Variante einsetzen zu können.
das Problem ist der "Verwaltungsaufwand" der entsteht, wenn du einen Zellwert änderst.
Änderst du mehrere Zellen gleichzeitig, dann kann Excel diese Aufgabe für alle Zellen gebündelt im Block durchführen, änderst du jedes Zelle einzeln, muss auch jedesmal dieser "Overhead" ausgeführt werden

ich meine das so:
Sub Test2()

Dim ko1 As Long
Dim ko2 As Long
Dim az As Long
Dim lz2 As Long


ko1 = 18
ko2 = 19

Sheets(1).Select
Cells(1, 79).Value = "Hilfsko_alt"
Cells(1, 80).Value = "Hilfsko_neu"

az = 2
lz2 = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 79), Cells(lz2, 80))
    .Columns(1).FormulaR1C1 = "=Text(RC" & ko1 & ",""00-"")&Text(RC" & ko2 & ",""00"")"
    .Columns(2).FormulaR1C1 = "=VlookUp(RC[-1],Fachklassen!C1:C5,5,0)"
    .Copy
    .PasteSpecial xlpastevalues
End With

With Range(Cells(2, ko1), Cells(lz2, ko1))
    .FormulaR1C1 = "=left(RC80,2)"
    .Formula = .Value
End With
With Range(Cells(2, ko2), Cells(lz2, ko2))
    .FormulaR1C1 = "=right(RC80, 2)"
    .Formula = .Value
End With

End Sub
im Prinzip kannst du dir die Hilfsspalte 79 auch sparen und die Formel aus Spalte 79 auch noch in die Formel für Spalte 80 einsetzen.


mit forgeschrittener Programmiertechnik kannst du dir Hilfsspalten sparen.
hier wird zwar auch mit einer Schleife gearbeitet, aber die Berechnung wird nicht mit den Tabellenblattwerden ausgeführt, sondern in einem Array in das die Tabellenblattwerte kopiert wurden.
beim Rückschreiben wird dann auch das Ergebnisarray in einem Schritt zurückgeschrieben, so dass die Anzahl der Zellwertänderungen (gleichzeitige als 1 gezählt) minimal ist.
der SVerweis wird hier durch das schnellere Dictionary-Objekt ersetzt.
Sub Test3()
Dim dic
Dim arr
Dim z As Long
Dim ID As String
Dim Erg As String

arr = Sheets("Fachklassen").UsedRange.Resize(, 5).Value
Set dic = CreateObject("scripting.dictionary")
For z = 2 To UBound(arr, 1)
    dic(arr(z, 1)) = arr(z, 5)
Next

With Sheets(1).UsedRange.Columns(18).Resize(, 2)
    arr = .Value
    For z = 2 To UBound(arr, 1)
        ID = Format(arr(z, 1), "00") & "-" & Format(arr(z, 2), "00")
        Erg = dic(ID)
        arr(z, 1) = Left(Erg, 2)
        arr(z, 2) = Right(Erg, 2)
    Next
    .Value = arr
End With

End Sub
wobei die Geschwindigkeitsunterschiede dann relvant werden, wenn die Listen mal fünfstellige Zeilenanzahlen erreichen.

Gruß Daniel

Betrifft: AW: Sverweis via VBA
von: Matthias
Geschrieben am: 23.10.2020 13:20:37

Hallo Daniel,

super, wieder mal etwas dazugelernt, danke dir :-)

Betrifft: AW: Sverweis via VBA
von: Herbert_Grom
Geschrieben am: 23.10.2020 11:32:41

Hallo Matthias,

Standard-Makros haben in "ClassModule" nix zu suchen!

Das gesuchte Ergebnis für "Hilfsko_neu" hast du doch in den Spalten R&S des aktiven Sheets schon stehen. Warum suchst du es dann umständlich noch aus dem 2. Sheet?

Servus

Betrifft: AW: Sverweis via VBA
von: Matthias
Geschrieben am: 23.10.2020 11:47:26

Hi Herbert,

eben nicht, dass sind andere Zahlen, der Spalteninhalt wird ersetzt :-)

Problem ist mittlerweile allerdings gelöst.

LG

Betrifft: AW: Sverweis via VBA
von: Herbert_Grom
Geschrieben am: 23.10.2020 11:50:33

Hallo Matthias,

sorry, das habe ich zwischenzeitlich auch gesehen! Wer lesen kann ist echt im Vorteil!

Servus

Beiträge aus dem Excel-Forum zum Thema "Sverweis via VBA"