Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Image
BildScreenshot zu Image Image-Seite mit Beispielarbeitsmappe aufrufen

zu langer SVERWEIS in VBA möglich? | Herbers Excel-Forum


Betrifft: zu langer SVERWEIS in VBA möglich? von: Holger Haase
Geschrieben am: 04.01.2010 09:12:06

Hallo liebe Leute,

hab diese Frage schon bei "Office-Lösung" gestellt, mir konnte aber nicht geholfen werden.

Folgendes Problem:

ich möchte mit einem SVERWEIS in ca. 15 verschiedenen Tabellen suchen. In Spalte A ist mein Suchkriterium und in Spalte B ein Begriff (Ort). Ich möchte also sagen, wenn in B Ort 1 steht, dann sverweis auf Tabelle 1; wenn in B Ort 2 steht, dann sverweis auf Tabelle 2 usw.

Das scheitert ohne vba daran, dass die Formellänge auf 1024 Zeichen beschränkt.
Mit VBA scheitert es aber auch, bei einer bestimmten Länge, wahrscheinlich weil es ja auch nur in die Zelle in Excel reingeschrieben wird.

Soweit bin ich erst mal mit aufzeichnen gekommen, das ganze ist im Editor auch schlecht lesbar, weil ich nicht weiß, ob und wo ich einen Zeilenumbruch (ich weiß: lerr+_) einfügen kann.

Statt "Tabelle1" steht bei mir natürlich der ganze Pfad und der ist lang, weswegen die vielen Zeichen entstehen. Hier mein Beispiel:

Range("U5").Select
ActiveCell.Formula = _
"=IF($B5=""Ort 1"", IF(VLOOKUP($A5,'Tabelle1'!$B$1:$AQ$90,25,FALSE)=FALSE,"""",""ja""), IF($B5=""Ort2"", IF(VLOOKUP($A5,'Tabelle2'!$B$1:$AQ$90,25,FALSE)=FALSE,"""",""ja""), IF($B5=""Ort3"", IF(VLOOKUP($A5,'Tabelle3'!$B$1:$AQ$90,25,FALSE)=FALSE,"""",""ja""), IF(VLOOKUP($A5,'Tabelle4'!$B$1:$AQ$90,25,FALSE)=FALSE,"""",""ja""))"
Range("U5").Select
Selection.AutoFill Destination:=Range("U5:U90"), Type:=xlFillDefault
Range("U5:U90").Select
Range("T30").Select

Ich hoffe mir kann jemand helfen, ich habe schon lange nach Lösungen gesucht aber nichts gefunden.

Gruß Holger

  

Betrifft: AW: zu langer SVERWEIS in VBA möglich? von: Klaus-Dieter
Geschrieben am: 04.01.2010 09:32:17

Hallo Holger,

man kann natürlich versuchen, mit VBA Formeln in Zellen zu schreiben. Allerdings nutzt man dabei nicht das Potential, das man dort haben kann.
Deinen Beitrag hatte ich bei Office-Lösung gesehen, war aber aus der Beschreibung nicht richtig schlau geworden. Ich werde versuchen, das dort noch einmal zu finden.
Gibt es eine Gesetzmäßigkeit, bezogen auf den Suchbegriff, aus der man ableiten kann, in welcher Tabelle der gesuchte Begriff sein müsste?



Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Online-Excel



  

Betrifft: AW: zu langer SVERWEIS in VBA möglich? von: Holger Haase
Geschrieben am: 04.01.2010 10:13:31

Hallo Klaus-Dieter,

danke für die schnelle Antwort.
Nein, es gibt keine Gesetzmäßigkeit, der Suchbegriff ist eine Nummer, der in einer der 15 Tabellen in der 1. Spalte der Suchmatrix steht. Der Ort ist einfach ein Ortsname.

Und mir wäre es lieb, wenn VBA den SVERWEIS nicht in jede Zelle schreibt in der ich ein Ergebniss haben möchte. Ich würde es ja gern mit VBA versuchen, weil die Formel in der Zelle einfach zu lang ist.

Gruß

Holger


  

Betrifft: AW: zu langer SVERWEIS in VBA möglich? von: Klaus-Dieter
Geschrieben am: 04.01.2010 10:16:52

Hallo Holger,

wäre es möglich, dass du mir die Datei einmal zukommen lässt? Dann würde ich versuchen, eine Lösung zu finden. excel at klaus minus dieter minus 2000 Punkt de (at minus und Punkt durch die entsprechenden Zeichen ohne Leerzeichen ersetzen.



Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Online-Excel



  

Betrifft: AW: zu langer SVERWEIS in VBA möglich? von: Reinhard
Geschrieben am: 04.01.2010 11:12:24

Hallo Holger,

vielleicht mal so:

=IF(NOT(VLOOKUP($A5,INDIRECT("Tabelle"&VALUE(SUBSTITUTE(B5,"Ort",""""))-1&"!$B$1:$AQ$90"),25,0)) ,"""","ja")

Gruß
Reinhard


  

Betrifft: Deine Fml wird nur deshalb zu lang,... von: Luc:-?
Geschrieben am: 04.01.2010 15:39:11

...Holger,
weil du das xlFmlPotenzial weder zu kennen noch zu nutzen scheinst. Schon die vielen "FALSE" sind absolut überflüssig, wenn du Dann- und Sonst bei WENN (IF) vertauschen würdest. Bei VLOOKUP muss auch nicht zwingend FALSE verwendet wdn — es genügt 0. Außerdem ist die ganze IF-Struktur durch WAHL (CHOOSE) ersetzbar. Du musst nur gleich am Anfang mit einer Liste deiner Namen VERGLEICHen (MATCH). Damit hast du dann das Auswahlkriterium für die verschieden SVERWEISe (VLOOKUP). Da die alle gleich sind, sich nur durch den Dateinamen unterscheiden, musst du auch nur den mit WAHL bestimmen, d.h., nur ein VLOOKUP ist als Rahmenfkt nötig, alles andere spielt sich in deren 2.Argument ab...
...Und dann gäbe es ja immer noch die Möglichkeit, Fmln durch Benennung gleicher Fmlteile zu verkürzen. Aber das wird hier wohl kaum erforderlich sein.
Gruß Luc :-?


  

Betrifft: Falls deine Fml dann immer noch zu lang wird,... von: Luc:-?
Geschrieben am: 04.01.2010 16:01:43

...Holger,
kannst du ja die Ermittlung des Auswahlarguments (Arg1) von WAHL (CHOOSE) und/oder gleich die ganze WAHL-Konstruktion in eine solche benannte Fml auslagern, also ggf 2 benannte Fml verwenden...
OrteVgl:=VERGLEICH($B5;{"Ort1"."Ort2"."Ort3"}
DatBer:=WAHL(OrteVgl;'Tabelle2'!$B$1:$AQ$90;'Tabelle3'!$B$1:$AQ$90;'Tabelle4'!$B$1:$AQ$90)
U5:=WENN(SVERWEIS($A5;DatBer;25;0);"ja";"")
Gruß Luc :-?


  

Betrifft: AW: Falls deine Fml dann immer noch zu lang wird,... von: Holger Haase
Geschrieben am: 05.01.2010 09:59:42

Vielen Dank für die Anregung.
Das ist in der Tat Neuland für mich, damit muss ich mich erst mal auseinandersetzen. Könnte noch eine Weile dauern, bis ich da ein feedback gebe.
Die erste Frage die sich mir schon mal stellt, ist, ob dieses "Formel auslagern" in Excel funktioniert oder nur in VBA. In deinem ersten Beitrag klang es nach einer Lösung in Ecxel, bei deinem zweiten Beitrag bin ich mir da nicht mehr sicher.

LG
Holger


  

Betrifft: AW: Falls deine Fml dann immer noch zu lang wird,... von: Holger Haase
Geschrieben am: 05.01.2010 12:27:49

Hallo Luc,

also ich hab mir jetzt noch vier Hilfsspalten angelegt, in Z sind die Orte aufgelistet und in Y davor der Bezug des Ortes für den SVERWEIS, dann weiter hinten in AC hab ich die Formel für den Vergleich und in AD die Fml für die Wahl.
Bis dahin funktioniert es, Vergleich gibt mir die Zeilenzahl des Ortes in Z und Wahl gibt mir den Inhalt von Y.

Wenn ich jetzt aber den SVERWEIS in U schreibe (=SVERWEIS($A5;$AD5;25;0), dann gibt er mir #NV, ich vermute weil er $AD5 nicht als Bereich erkennt. Oder woran liegt das?

Holger


  

Betrifft: AW: Falls deine Fml dann immer noch zu lang wird,... von: Klaus-Dieter
Geschrieben am: 05.01.2010 12:58:41

Hallo Holger,

es macht keinen Sinn, den Suchbereich auf eine Zelle zu begrenzen. Zumal in deiner Formel der Wert aus der 25. Spalte des Suchbereichs gefragt ist. Das wäre eindeutig außerhalb des definierten Bereichs. Deshalb auch die Fehlermeldung.



Viele Grüße Klaus-Dieter

Klaus-Dieter's Excel und VBA Seite
Online-Excel



  

Betrifft: AW: Falls deine Fml dann immer noch zu lang wird,... von: Holger Haase
Geschrieben am: 05.01.2010 14:02:57

Hallo,

eigentlich völlig logisch, dass das so nicht geht ("den Suchbereich auf eine Zelle zu begrenzen"), aber wie ist den das mit Formel auslagern nun gemeint.

Wenn ich die ganze WAHL-Formel mit in den SVERWEIS reinschreib und für Wert1; Wert2 usw. (in WAHL) jeweils den ganzen Pfad zur Suchmatrix nehme, funktioniert das; jedoch nur für 8 Tabellen.
Also für 9 Pfade ist meine Formel dann immer noch zu lang.

Holger


  

Betrifft: Du sollst den anderen Formelteilen Namen... von: Luc:-?
Geschrieben am: 05.01.2010 15:05:04

...geben, Holger,
also Einfügen - Namen - Definieren — in die 1.Zeile der Box wird der Name eingetragen und in Bezug die Formel — genauso wie ich es dir aufgeschrieben habe. In die Fml im Blatt wird dann der Name eingesetzt.
Alternative: Namen in der TabBlattFml durch dessen Fml ersetzen, den Namen darin wieder durch dessen Fml (jeweils ohne 1.=) und hoffen, dass die Fml dann nicht zu lang wird.
Empfehlung: Mal in der xlHilfe unter Namen, definieren nachlesen!
Gruß Luc :-?
PS: Es handelt sich in beiden Antworten nur um Fmln, kein VBA!


  

Betrifft: AW: Du sollst den anderen Formelteilen Namen... von: Holger Haase
Geschrieben am: 05.01.2010 16:33:18

Hallo,

ok, jetzt hab ich's kapiert.

Hab jetzt der Übersicht halber den einzelnen Pfaden den Namen "Mess + Nummer aus dem Vergleich" gegeben und Abfrage wurde modifiziert, sodass jetzt der Wert erscheint, und wenn es keinen gibt, dann "nein".

=WENN(SVERWEIS($A5;WAHL($AC5;Mess1;Mess2;Mess3;Mess4;Mess5;Mess6;Mess7;Mess8;Mess9;Mess10;Mess11; Mess12;Mess13;Mess14;Mess15;Mess16;Mess17);74;0)=0;"nein";SVERWEIS(A5;WAHL($AC5;Mess1;Mess2;Mess3; Mess4;Mess5;Mess6;Mess7;Mess8;Mess9;Mess10;Mess11;Mess12;Mess13;Mess14;Mess15);25;0))

Da hat er ganz schön zu rechnen, aber so klappt es.

Vielen Dank all jenen, die sich den Kopf zerbrochen haben und besonders Dir Luc:-?

Holger


  

Betrifft: Naja, dann bitte sehr! Ahoi! owT von: Luc:-?
Geschrieben am: 05.01.2010 17:56:55

:-?


Beiträge aus den Excel-Beispielen zum Thema "zu langer SVERWEIS in VBA möglich?"