Microsoft Excel

Herbers Excel/VBA-Archiv

Index im VBA

Betrifft: Index im VBA von: Adrian Lüscher
Geschrieben am: 23.02.2021 22:03:31

Hallo zusammen,

Ich erweitere eine bestehende Excel-Tabelle und will gerne die Index-Funktion im VBA einsetzten.

Ausganslage ist mal, dass ich aus verschiedenen Quellen Daten hole.
Diese setzt ich alle im Blatt "Import" ein. (diese Tabelle ist dann immer unterschiedlich lang

Beim Blatt Lieferant finde ich die Lieferantennummer wieder in der Auto-Tabelle "Tabelle1"
Nun sollte in der Spalte P und Q die Werte herausgesucht werden über das Suchkriterium "LieferantN"
Je nach Länge der Tabelle muss die Formel entsprechend tiefer runtergezogen werden...

Mit etwas Starthilfe von Eurer Seite her würde ich dann in einem späteren Zeitpunkt andere Spalten noch ergänzen.

Anbei noch die Musterdatei:
https://www.herber.de/bbs/user/144187.zip

Danke vorab
Adrian

Betrifft: AW: Index im VBA
von: Werner
Geschrieben am: 23.02.2021 22:36:58

Hallo,
Sub Test()
Dim loLetzte As Long

With Worksheets("Import")
    loLetzte = .Cells(.Rows.Count, "D").End(xlUp).Row
    .Range(.Cells(5, "P"), .Cells(loLetzte, "P")).FormulaLocal = _
    "=INDEX(Tabelle1;VERGLEICH(Import!$F5;Tabelle1[LieferantN];0);SPALTE(Tabelle1[Wert1])- _
SPALTE(Tabelle1[Lieferant])+1)"
    .Range(.Cells(5, "Q"), .Cells(loLetzte, "Q")).FormulaLocal = _
    "=INDEX(Tabelle1;VERGLEICH(Import!$F5;Tabelle1[LieferantN];0);SPALTE(Tabelle1[Wert2])- _
SPALTE(Tabelle1[Lieferant])+1)"
End With

Den Zeilenumbruch in den beiden Formeln bitte wieder raus machen, wurde von der Forensoftware  _
eingefügt.

Gruß Werner

End Sub


Betrifft: AW: Index im VBA
von: Adrian Lüscher
Geschrieben am: 23.02.2021 23:07:50

Hallo Werner,

Vielen Dank für deine Lösung! :-)
Hat auf Anhieb geklappt.

Mein Ziel war zumindest, dass ich am Ende nur Werte drin habe und keine Formel.
Ich weiss man könnte nun noch "als Werte einfügen" dazupacken...

Der Einsatz ist später in einer sehr grossen Tabelle mit vielen Formeln.
Wollte eben ein Teil ins VBA "auslagern" :-)

Würde es mit der reinen VBA-Variante auch möglich sein?
So dass die Ausgabe von Anfang an ein Wert und keine Formel ist?

Gruss
Adrian

Betrifft: AW: Index im VBA
von: Werner
Geschrieben am: 23.02.2021 23:15:18

Hallo,

na und? Wo ist jetzt das Problem. Einfach die Formeln durch ihre Werte ersetzen und ggf. zu Beginn des Makros noch die automatische Formelberechung auf manuell setzen und am Schluß wieder auf automatisch.
Sub Test()
Dim loLetzte As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

With Worksheets("Import")
    loLetzte = .Cells(.Rows.Count, "D").End(xlUp).Row
    .Range(.Cells(5, "P"), .Cells(loLetzte, "P")).FormulaLocal = _
    "=INDEX(Tabelle1;VERGLEICH(Import!$F5;Tabelle1[LieferantN];0);SPALTE(Tabelle1[Wert1])- _
SPALTE(Tabelle1[Lieferant])+1)"
    .Range(.Cells(5, "Q"), .Cells(loLetzte, "Q")).FormulaLocal = _
    "=INDEX(Tabelle1;VERGLEICH(Import!$F5;Tabelle1[LieferantN];0);SPALTE(Tabelle1[Wert2])- _
SPALTE(Tabelle1[Lieferant])+1)"
    .Range(.Cells(5, "P"), .Cells(loLetzte, "Q")).Value = .Range(.Cells(5, "P"), .Cells( _
loLetzte, "Q")).Value
End With

Application.Calculation = xlCalculationAutomatic
End Sub
Gruß Werner

Betrifft: AW: Index im VBA
von: Adrian Lüscher
Geschrieben am: 23.02.2021 23:27:09

Hallo Werner,

Danke Dir für deine Anpassungen.
Ich habe mich wahrscheinlich zu unklar ausgedrückt.
Ich hatte in der Vergangenheit eine sehr grosse Tabelle, bei welche ich über mehrere Blätter die Daten verknüpft hatte, verarbeitet hatte und das Ergebnis wieder zurückschicke.

Das gab je nach Anpassung eine relativ lange Berechnungszeit :-(
Mein Ziel war nun, dass nicht alle Formen sich immer wieder nachrechnen.
Aus diesem Grund wollte ich einen Teil ins VBA "auslagern"

Darum das Augenmerk auf die VBA-Lösung

Ich weiss, es gibt die Funktion Index + Match im VBA.
Muss aber ehrlich gestehen, dass ich diese noch nicht so ganz verstehe...

Kennst Du diese Variante?

Gruss
Adrian

Betrifft: AW: Index im VBA
von: Daniel
Geschrieben am: 24.02.2021 11:04:41

Hi
wenn was mit Formeln gut berechnet werden kann, ist es auch per VBA meistens eine gute Lösung, die Formeln in die Zellen zu schreiben und ggf die Formelergebnisse durch die Werte zu ersetzen.
will man rein in VBA berechnen und dabei schneller sein als die Formellösung, wird es meistens etwas aufwendiger, weil die Berechnung zunächst in einem Array durchführen sollte um dann das Ergebnis in einem Schritt in die Zellen zurück zu schreiben, und man muss öfters mal auf fortgeschrittene Programmiertechniken wie das Dictionary-Objekt zurückgreifen, um schneller zu sein als Formellösungen.

insbesondere wenn Funktionen wie SVerweis und Index+Vergleich verwendet werden, hat man bei dem Lösungsweg "Formel per Makro" einein einfachen Stellhebel, um die Geschwindigkeit dramatisch zu verbessern:
- Liste sortieren und dann SVerweis mit 4. Parameter = WAHR bzw Vergleich mit 3. Parameter = 1 verwenden, die bei großen Listen um Welten schneller sind ihre Varianten für unsortierte Listen. eine derartige schnelle Suche in sortierten Listen ist derzeit in VBA noch nicht implementiert und kann nur über diese beiden Funktionen genutzt werden (bzw in VBA dann über Worksheetfuncition...)

Gruß Daniel

Betrifft: AW: Index im VBA
von: ralf_b
Geschrieben am: 23.02.2021 22:57:28

und zum vba noch eine einfachere Formel,
gib die Zielspalte direkt bei index an, dann mußt du nicht noch die Spalte ausrechnen
übrigens stimmt in der Importtabelle die Zuordnung Liefrant - lierferantN nicht mit der aus Tabelle1 überein.
=INDEX(Tabelle1[Wert1];VERGLEICH(F5;Tabelle1[LieferantN];0))

gruß
rb

Betrifft: Gesucht ist sicher WorksheetFunction.Index, ...
von: Luc:-?
Geschrieben am: 24.02.2021 00:48:45

…Adrian,
aber das ist dann die reine Fkt mit ihren ursprünglichen Einschränkungen bei allen Argumenten außer ihrem ersten. Möglicherweise ist dir das deshalb nicht empfohlen worden, denn in Xl wdn Fmln noch vom FmlText-Interpreter behandelt und ggf rekursiert, was man in VBA selber pgmmieren muss.
Allerdings kannst du die Fml mal in US-Original-Notation mit der vbFkt/-Methode Evaluate berechnen lassen. Die bezieht auch den FmlText-Interpreter zumindest teilweise ein und liefert dann gleich einen Wert, den du ins Blatt als solchen übernehmen kannst. Allerdings klappt das, besonders im Zusammenhang mit INDEX, nicht immer.
Morhn, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon

Betrifft: AW: Gesucht ist sicher WorksheetFunction.Index, ...
von: Adrian Lüscher
Geschrieben am: 26.02.2021 08:03:16

Danke für Eure Antworten.

Ich konnte technisch nicht immer alles nachvollziehen :-)
Aber wenn ich es richtig verstanden habe, habe ich keine Performance-Vorteile?

Dann kann man es wirklich auch wie vorgeschlagen lösen - so dass man am Ende noch die Werte einfügt.

Ich habe testweise (ganz vereinfacht) mit der "WorksheetFunction.Index" noch ein Muster gemacht.
Kann es aber noch nicht ganz auf meine ursprüngliche Anforderung umschreiben.

Man müsste ja dann einen Loop erzeugen in Zeilen und Spaltenrichtung?

Gruss
Adrian

Betrifft: AW: Gesucht ist sicher WorksheetFunction.Index, ...
von: Yal
Geschrieben am: 26.02.2021 09:04:36

Hallo Adrian,

wie bereit bei deinem anderen Frage erwähnt (https://www.herber.de/forum/messages/1815965.html ), da dein Problem am Rand der relationale Behandlung liegt, spare Dir Formeln und VBA und gehe auf Power Query.
Das Einlernen wäre genau in deinem Fall gut investierte Zeit, da Formeln und VBA beim Wartung nicht ganz unproblematisch sind.

VG
Yal