Excel-VBA - In Range alle Zwischenwerte von Min bis Max suchen

In Excel2003 möchte ich mit VBA aus einer Spalte die Referenzen aller Werte zwischen Minimum und Maximum in Variable oder einen Array speichern.

- Spalte enthält Zahlen im Format 0.0000, sie werden nach Bedarf sortiert. Gleiche Zahlen können mehrmals vorkommen.
- VBA liefert selbst die Min- und Max-Werte und erwartet ALLE Zwischenwerte zurück.

Mit SVerweis (in VBA = VLookup) >>> Application.WorksheetFunction.VLookup(dblMaxWert, [A3:C875], 3)
nach Max-Wert suchen, liefert Angaben zum höchsten Wert unter dem MaxWert (oder zum MaxWert, wenn vorhanden)
VLookup mit dem Zahlen-Wert zu dieser 1.Antwort (- 0.0001) liefert den nächst tieferen Wert...
...bis der Min-Wert unterschritten wird.

Wenn Min/Max gleich 7/9 ist, liefert mir der erste SVerweis mit 9 vielleicht eine Antwort auf den Wert 8.7682
Der zweite Durchgang sucht dann mit 8.7681 ...

Eine umständliche Sache, aber das eigentliche Problem ist >>>
> Wenn mehrere gleiche Zahlen in der Suchspalte stehen, erhalte ich mit dieser Methode nur eine davon !

Vielleicht habe ich ja nur Scheuklappen auf, und übersehe eine vernünftige Lösung...
Gibt es eine solche Lösung ?

Danke und Gruss,
Urs

 

Danke Hascheff,

Ich hatte alle vorhandenen Funktionen geprüft... und RANG übersehen !

Man kann die meisten Excel-Funktionen in VBA benutzen, allerdings mit dem englischen Namen, hier ist es RANK. Das ist etwa die gleiche Funktion wie VERGLEICH (VBA = MATCH), nur braucht sie keine sortierte Liste.

Beide haben hier auch die gleiche Unzulänglichkeit: Sie brauchen eine Suchvorgabe, die in der Liste tatsächlich vorkommt. Und die kann mir nur SVerweis liefern, der dann wiederum Doppeleinträge übersieht...

Es geht übrigens um XY-Koordinaten. Es soll geprüft werden ob überhaupt, und wenn ja welche vorhandenen Punkte in der Nähe des errechneten Punktes liegen. Zuerst wird auf einer Achse zwischen Min und Max gesucht und dann geprüft, ob der gefundene Punkt auch auf der anderen Achse zwischen Min und Max liegt.
Die Distanzberechnung liefert dann den nächstliegenden Punkt...

Ich hätte nie gedacht, dass diese "einfache" Suche so kompliziert wird.
Ich werde mal versuchen, dem von SVERWEIS gelieferten Wert mit VERGLEICH die Zeilennummer zuzuweisen. Wenn beim nächsten SVerweis eine Zeile fehlt, kann ich die dazwischen liegenden Mehrfacheinträge direkt ansprechen und auslesen...
...oder ich lasse nach dem ersten SVerweis die nächsten Zeilen direkt im Blatt prüfen und dann auslesen... mal sehen...

Gruss Urs

 

Hi Urs,

weshalb prüfst du nicht in einer Schleife von unten nach oben (oder oben nach unten), ob der Wert zwischen dem laufenden und dem nächsten Wert liegt?

 

Hallo Karin und Hascheff,

Mit der Schleife Tausende von Tabellenzeilen zu bearbeiten widerstrebt mir, zumal eine äussere Schleife schon besteht, die das Ganze für an die hundert verschiedene Ausgangswerte durchrechnen lässt.

Distanz und Richtung für alle Punkte zu berechnen ist so auch zu aufwendig. Diese Funktion alleine hat mit ihrer eigenen Schleife schon mehrere hundert Codezeilen abzuarbeiten, die Erde ist ja ein Ellipsoid...

Aber eine Mischung aus einem SVerweis+Vergleich für die MaxWert-Zeile und dann der Schleife für die tieferen Werte bis zur MinWert-Zeile scheint tatsächlich wohl am wenigsten unnötig rechnen zu müssen und damit am schnellsten sein.
Die relativen Distanzen können dann für diese kleine Menge berechnet werden, wobei für die kurzen Distanzen das Ellipsoid überflüssig ist.

Ich werde mich mal dran machen...

Danke und Gruss,
Urs

 

Hi Urs,

wenn du bei VLOOKUP den Operator auf 1 setzt, dann wird der nächst kleinere Wert angezeigt. Könnte das vielleicht helfen?

 

Hi Karin,

Richtig, mit VLOOKUP habe ich den Wert. Ich habe aber noch einen einfacheren Weg gefunden mit VERGLEICH/MATCH.

MATCH arbeitet genau gleich wie VLOOKUP, liefert aber die Zeilennummer zurück.
Mit dieser Zeilennummer können dann in einer Schleife auch die weiteren Zeilen (auch Zeilen mit gleichen Werten) bis zum MinimumWert direkt angesprochen, geprüft und gespeichert werden.

Es macht langsame Fortschritte... zum Glück ist es nur eine Komfort-Ergänzung eines schon funktionierenden Programms...

Danke und Gruss,
Urs

 

Hi Urs,

Bisher war somit nicht die Rede davon, dass du irgendeine Zeilenposition suchst, sondern immer nur einem "Wert". Es ist logisch, dass mittels VLOOKUP nur ein Wert wiedergegeben wird, aber keine Zeilenposition (s. Tabellenblattfunktion SVERWEIS()). Vielleicht solltst du deine Fragestellung eindeutiger formulieren, was du wirklich erreichen willst, dann wird es einfacher, darauf zu antworten.

 

Hallo Karin,

Natürlich will ich an die Werte, aber um die auslesen zu können, brauche ich eine erste Zeilenposition.
VLOOKUPs in Kaskade überspringen ja mehrfach vorhandene gleiche Werte. Nichts anderes war meine Frage als TO >

Mit dem gezielten Auslesen der auf die erhaltene Zeilenposition folgenden Zeilen erhalte ich jetzt alle Werte. Es funktioniert !

Auch wenn Hinweise auf den ersten Blick nicht zielgerichtet erscheinen, können sie wertvoll sein. Sie öffnen Denkblockaden...
...und das haben sie hier getan. Danke !

Gruss Urs